MariaDB Xpand Secondary Indexes

A table's secondary indexes are used to speed up query execution.

Overview

MariaDB Xpand supports secondary indexes:

  • The behavior of secondary indexes with Xpand is identical to the behavior of secondary indexes with InnoDB.

  • Xpand uses secondary indexes to generate additional representations of the table on disk.

  • Representations from secondary indexes are sliced and replicated through the Xpand deployment.

This documentation applies to both Xpand topologies. Most examples are designed for the Xpand Performance Topology. However, the examples explicitly mention any differences that are relevant for the Xpand Storage Engine Topology.

Examples

Create a Table with a Secondary Index

To create a table with a secondary index, use the CREATE TABLE statement 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)
);

Note

If you are using an Xpand Storage Engine Topology, you must also specify ENGINE=Xpand, unless default_storage_engine is set to Xpand.

Creating Tables with Single Column Secondary Index

Let's create an Xpand table with a single column secondary index:

  1. Connect to the server using MariaDB Client:

    $ mysql --user=root --password --no-auto-rehash
    
  2. Use the CREATE DATABASE statement to create the database:

    CREATE DATABASE hq_sales;
    

    Note

    In a Xpand Storage Engine Topology, the ES nodes do not synchronize databases automatically. MariaDB Replication can be used to synchronize databases and other objects. Without replication, the database needs to be created separately on each ES node.

  3. Use the CREATE TABLE statement to create the table, and specify the INDEX clause to create the index:

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

    Note

    If you are using an Xpand Storage Engine Topology, you must also specify ENGINE=Xpand, unless default_storage_engine is set to Xpand.

Creating Tables with Composite Secondary Index

Let's create an Xpand table with a composite (multi-column) secondary index

  1. Connect to the server using MariaDB Client:

    $ mysql --user=root --password --no-auto-rehash
    
  2. Use the CREATE DATABASE statement to create the database:

    CREATE DATABASE hq_sales;
    

    Note

    In a Xpand Storage Engine Topology, the ES nodes do not synchronize databases automatically. MariaDB Replication can be used to synchronize databases and other objects. Without replication, the database needs to be created separately on each ES node.

  3. Use the CREATE TABLE statement to create the table, and specify the INDEX clause to create the secondary index:

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

    Note

    If you are using an Xpand Storage Engine Topology, you must also specify ENGINE=Xpand, unless default_storage_engine is set to Xpand.

Creating Tables with Secondary Index on Column Prefix

Let's create an Xpand table with a secondary index on a single column prefix:

  1. Connect to the server using MariaDB Client:

    $ mysql --user=root --password --no-auto-rehash
    
  2. Use the CREATE DATABASE statement to create the database:

    CREATE DATABASE hq_sales;
    

    Note

    In a Xpand Storage Engine Topology, the ES nodes do not synchronize databases automatically. MariaDB Replication can be used to synchronize databases and other objects. Without replication, the database needs to be created separately on each ES node.

  3. Use the CREATE TABLE statement to create the table, and specify the INDEX clause to create a secondary index:

    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.

    Note

    If you are using an Xpand Storage Engine Topology, you must also specify ENGINE=Xpand, unless default_storage_engine is set to Xpand.

Adding Secondary Indexes

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

  1. Connect to the server using MariaDB Client:

    $ mysql --user=root --password --no-auto-rehash
    
  2. Use the CREATE DATABASE statement to create the database:

    CREATE DATABASE hq_sales;
    

    Note

    In a Xpand Storage Engine Topology, the ES nodes do not synchronize databases automatically. MariaDB Replication can be used to synchronize databases and other objects. Without replication, the database needs to be created separately on each ES node.

  3. Use the CREATE TABLE statement to create the table:

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

    Note

    If you are using an Xpand Storage Engine Topology, you must also specify ENGINE=Xpand, unless default_storage_engine is set to Xpand.

  4. 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 Secondary Indexes

Let's drop the secondary index from the table created in the Creating Xpand Table with Single Column Secondary Index section:

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

    ALTER TABLE hq_sales.invoices DROP INDEX invoice_date;
    

Rebuilding Secondary Indexes

Let's rebuild the secondary index in the table created in the Creating Xpand Table with Single Column Secondary Index section:

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

    ALTER TABLE hq_sales.invoices DROP INDEX invoice_date;
    
  2. 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);