SPATIAL INDEX
Description
On MyISAM, Aria and InnoDB tables, MariaDB can create spatial indexes (an R-tree index) using syntax similar to that for creating regular indexes, but extended with the SPATIAL
keyword. Columns in spatial indexes must be declared NOT NULL
.
Spatial indexes can be created when the table is created, or added after the fact:
CREATE TABLE geom (g GEOMETRY NOT NULL, SPATIAL INDEX(g));
ALTER TABLE geom ADD SPATIAL INDEX(g);
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:
ALTER TABLE geom DROP INDEX g;
DROP INDEX sp_index ON geom;
Data-at-Rest Encryption
If innodb_checksum_algorithm is set to full_crc32
or strict_full_crc32
, and if the table does not use ROW_FORMAT=COMPRESSED, InnoDB spatial indexes are encrypted if the table is encrypted.
See MDEV-12026 for more information.
This page is licensed: GPLv2, originally from fill_help_tables.sql
Last updated
Was this helpful?