`SPATIAL` Indexes with MariaDB Enterprise Server

This page is part of MariaDB's Documentation.

The parent of this page is: Indexes for MariaDB Enterprise Server

Topics on this page:

Overview
Compatibility
Creating an InnoDB Table with a Single Column Spatial Index
Adding a Spatial Index to an InnoDB Table
Dropping a Spatial Index from an InnoDB Table
Rebuilding a Spatial Index in an InnoDB Table

Overview

A table's spatial indexes are used to speed up query execution for queries that search geometric data. Some details about spatial indexes with InnoDB:

MariaDB Community Server can have up to 64 total indexes for a given table.
MariaDB Enterprise Server can have up to 128 total indexes for a given table.
InnoDB stores secondary indexes in the same tablespace file as the clustered index and data.
Spatial indexes are R-trees, which are data structures that are designed specifically for searching spatial data. The key is a 2-dimensional Minimum Bounding Rectangle of the shape.
Spatial indexes are used to perform exact-value searches and range scans of columns that use the GEOMETRY, POINT, POLYGON, LINESTRING, MULTIPOINT, MULTILINESTRING, MULTIPOLYGON, and GEOMETRYCOLLECTION data types.
Columns must be declared NOT NULL to be indexed with a spatial index.
Spatial indexes do not support composite (multi-column) indexes.

Compatibility

MariaDB Enterprise Server 10.2
MariaDB Enterprise Server 10.3
MariaDB Enterprise Server 10.4
MariaDB Enterprise Server 10.5
MariaDB Enterprise Server 10.6

Creating an InnoDB Table with a Single Column Spatial Index

Let's create an InnoDB table with a single column spatial index after confirming that the default storage engine is InnoDB:

Connect to the server using MariaDB Client:
```
$ mariadb --user=root
```

Confirm that the default storage engine is InnoDB by checking the default_storage_engine system variable using the SHOW SESSION VARIABLES statement:

SHOW SESSION VARIABLES
   LIKE 'default_storage_engine';

+------------------------+--------+
| Variable_name          | Value  |
+------------------------+--------+
| default_storage_engine | InnoDB |
+------------------------+--------+

If the database does not exist, then create the database for the table using the CREATE DATABASE statement:
```
CREATE DATABASE hq_sales;
```

Create the table using the CREATE TABLE statement and specify the spatial index with the SPATIAL INDEX() clause:

CREATE TABLE hq_sales.branches (
   branch_id BIGINT UNSIGNED NOT NULL,
   branch_name VARCHAR(100),
   branch_location GEOMETRY NOT NULL,
   branch_address1 VARCHAR(100),
   branch_address2 VARCHAR(100),
   branch_address3 VARCHAR(100),
   branch_city VARCHAR(50),
   branch_state VARCHAR(50),
   branch_postal_code VARCHAR(15),
   branch_country VARCHAR(100),
   PRIMARY KEY(branch_id),
   SPATIAL INDEX(branch_location)
);

Adding a Spatial Index to an InnoDB Table

Let's create an InnoDB table without a spatial index, and then add a spatial index to it:

Connect to the server using MariaDB Client:
```
$ mariadb --user=root
```

Confirm that the default storage engine is InnoDB by checking the default_storage_engine system variable using the SHOW SESSION VARIABLES statement:

SHOW SESSION VARIABLES
   LIKE 'default_storage_engine';

+------------------------+--------+
| Variable_name          | Value  |
+------------------------+--------+
| default_storage_engine | InnoDB |
+------------------------+--------+

If the database does not exist, then create the database for the table using the CREATE DATABASE statement:
```
CREATE DATABASE hq_sales;
```

Create the table without a primary key using the CREATE TABLE statement:

CREATE TABLE hq_sales.branches (
   branch_id BIGINT UNSIGNED NOT NULL,
   branch_name VARCHAR(100),
   branch_location GEOMETRY NOT NULL,
   branch_address1 VARCHAR(100),
   branch_address2 VARCHAR(100),
   branch_address3 VARCHAR(100),
   branch_city VARCHAR(50),
   branch_state VARCHAR(50),
   branch_postal_code VARCHAR(15),
   branch_country VARCHAR(100),
   PRIMARY KEY(branch_id)
);

Alter the table using the ALTER TABLE statement and specify the new spatial index with the ADD SPATIAL INDEX() clause:
```
ALTER TABLE hq_sales.branches ADD SPATIAL INDEX (branch_location);
```

Dropping a Spatial Index from an InnoDB Table

Let's drop the spatial index from the table created in the Creating an InnoDB Table with a Single Column Spatial Index section:

Obtain the name of the index by joining the information_schema.INNODB_SYS_INDEXES, information_schema.INNODB_SYS_TABLES, and information_schema.INNODB_SYS_FIELDS tables:

SELECT isi.NAME AS index_name, isf.NAME AS index_column
FROM information_schema.INNODB_SYS_INDEXES isi
JOIN information_schema.INNODB_SYS_TABLES ist
ON isi.TABLE_ID = ist.TABLE_ID
JOIN information_schema.INNODB_SYS_FIELDS isf
ON isi.INDEX_ID = isf.INDEX_ID
WHERE ist.NAME = 'hq_sales/branches'
ORDER BY isf.INDEX_ID, isf.POS;

+-----------------+-----------------+
| index_name      | index_column    |
+-----------------+-----------------+
| PRIMARY         | branch_id       |
| branch_location | branch_location |
+-----------------+-----------------+

Alter the table using the ALTER TABLE statement and specify the DROP INDEX clause:
```
ALTER TABLE hq_sales.branches DROP INDEX branch_location;
```

Rebuilding a Spatial Index in an InnoDB Table

Let's rebuild the spatial index from the table created in the Creating an InnoDB Table with a Single Column Spatial Index section: