SPATIAL INDEX
Description
On MyISAM and Aria tables, as well as on InnoDB tables from MariaDB 10.2.2, MariaDB can create spatial indexes (an R-tree index) using syntax similar to that for creating regular indexes, but extended with the SPATIAL
keyword.
Currently, columns in spatial indexes must be declared NOT NULL
.
Spatial indexes can be created when the table is created, or added after the fact like so:
- with CREATE TABLE:
CREATE TABLE geom (g GEOMETRY NOT NULL, SPATIAL INDEX(g));
- with ALTER TABLE:
ALTER TABLE geom ADD SPATIAL INDEX(g);
- with CREATE INDEX:
CREATE SPATIAL INDEX sp_index ON geom (g);
SPATIAL INDEX
creates an R-tree
index. For storage
engines that support non-spatial indexing of spatial columns, the engine
creates a B-tree
index. A B-tree
index on spatial values is useful for
exact-value lookups, but not for range scans.
For more information on indexing spatial columns, see CREATE INDEX.
To drop spatial indexes, use ALTER TABLE or DROP INDEX:
- with ALTER TABLE:
ALTER TABLE geom DROP INDEX g;
- with DROP INDEX:
DROP INDEX sp_index ON geom;