Secondary Indexes with MariaDB Enterprise Server

Overview

A table's secondary indexes are used to speed up query execution. Some details about secondary 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.

  • With the exception of spatial indexes, secondary indexes are B+ trees, which are very efficient for searching for exact values, performing range scans, and checking uniqueness.

  • Secondary indexes use copy-on-write semantics for MVCC. Old secondary index records are delete-marked and permanently removed by the InnoDB Purge Threads.

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 Secondary Index

Let's create an InnoDB table with a single column secondary 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 secondary index with the INDEX() clause:

    CREATE TABLE hq_sales.invoices (
   invoice_id BIGINT UNSIGNED NOT NULL,
   branch_id INT NOT NULL,
   customer_id INT,
   invoice_date DATETIME(6),
   invoice_total DECIMAL(13, 2),
   payment_method ENUM('NONE', 'CASH', 'WIRE_TRANSFER', 'CREDIT_CARD', 'GIFT_CARD'),
   PRIMARY KEY(invoice_id),
   INDEX(invoice_date)
);

Creating an InnoDB Table with a Composite Secondary Index

Let's create an InnoDB table with a composite (multi-column) secondary 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 secondary index with the INDEX() clause:

    CREATE TABLE hq_sales.invoices (
   invoice_id BIGINT UNSIGNED NOT NULL,
   branch_id INT NOT NULL,
   customer_id INT,
   invoice_date DATETIME(6),
   invoice_total DECIMAL(13, 2),
   payment_method ENUM('NONE', 'CASH', 'WIRE_TRANSFER', 'CREDIT_CARD', 'GIFT_CARD'),
   PRIMARY KEY(invoice_id),
   INDEX(invoice_date, customer_id)
);

Creating an InnoDB Table with a Secondary Index on a Column Prefix

Let's create an InnoDB table with a secondary index on a single column prefix 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 secondary index with the INDEX() clause:

    CREATE TABLE hq_sales.customers (
   customer_id BIGINT AUTO_INCREMENT NOT NULL,
   customer_name VARCHAR(500),
   customer_email VARCHAR(200),
   PRIMARY KEY(customer_id),
   INDEX(customer_name(100))
);

    • The secondary index is specified with the customer_name(100) prefix, so only the first 100 characters of the customer_name column for each row will be indexed.

Adding a Secondary Index to an InnoDB Table

Let's create an InnoDB table without a secondary index, and then add a secondary 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.invoices (
   invoice_id BIGINT UNSIGNED NOT NULL,
   branch_id INT NOT NULL,
   customer_id INT,
   invoice_date DATETIME(6),
   invoice_total DECIMAL(13, 2),
   payment_method ENUM('NONE', 'CASH', 'WIRE_TRANSFER', 'CREDIT_CARD', 'GIFT_CARD'),
   PRIMARY KEY(invoice_id)
);

  5. Alter the table using the ALTER TABLE statement and specify the new secondary index with the ADD INDEX() clause:

    ALTER TABLE hq_sales.invoices ADD INDEX (invoice_date);

Dropping a Secondary Index from an InnoDB Table

Let's drop the secondary index from the table created in the Creating an InnoDB Table with a Single Column Secondary 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/invoices'
ORDER BY isf.INDEX_ID, isf.POS;

    +--------------+--------------+
| index_name   | index_column |
+--------------+--------------+
| PRIMARY      | invoice_id   |
| invoice_date | invoice_date |
+--------------+--------------+

  2. Alter the table using the ALTER TABLE statement and specify the DROP INDEX clause:

    ALTER TABLE hq_sales.invoices DROP INDEX invoice_date;

Rebuilding a Secondary Index in an InnoDB Table

Let's rebuild the secondary index in the table created in the Creating an InnoDB Table with a Single Column Secondary 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/invoices'
ORDER BY isf.INDEX_ID, isf.POS;

    +--------------+--------------+
| index_name   | index_column |
+--------------+--------------+
| PRIMARY      | invoice_id   |
| invoice_date | invoice_date |
+--------------+--------------+

  2. Alter the table using the ALTER TABLE statement and specify the DROP INDEX clause:

    ALTER TABLE hq_sales.invoices DROP INDEX invoice_date;

  3. Alter the table using the ALTER TABLE statement and specify the secondary index with the ADD INDEX() clause:

    ALTER TABLE hq_sales.invoices ADD INDEX (invoice_date);