MariaDB Enterprise Server InnoDB Secondary Indexes

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.

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);