MariaDB Enterprise Server InnoDB Spatial Indexes

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.

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:

  1. Connect to the server using MariaDB Client:

    $ mariadb --user=root
    
  2. 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 |
    +------------------------+--------+
    
  3. If the database does not exist, then create the database for the table using the CREATE DATABASE statement:

    CREATE DATABASE hq_sales;
    
  4. 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:

  1. Connect to the server using MariaDB Client:

    $ mariadb --user=root
    
  2. 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 |
    +------------------------+--------+
    
  3. If the database does not exist, then create the database for the table using the CREATE DATABASE statement:

    CREATE DATABASE hq_sales;
    
  4. 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)
    );
    
  5. 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:

  1. 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 |
    +-----------------+-----------------+
    
  2. 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:

  1. 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 |
    +-----------------+-----------------+
    
  2. Alter the table using the ALTER TABLE statement and specify the DROP INDEX clause:

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

    ALTER TABLE hq_sales.branches ADD SPATIAL INDEX (branch_location);