Index Options with MariaDB Xpand

Overview

MariaDB Xpand's index options are used in CREATE TABLE, ALTER TABLE, and CREATE INDEX statements to define index characteristics.

Compatibility

  • MariaDB Xpand 5.3

  • MariaDB Xpand 6.0

  • MariaDB Xpand 6.1

CREATE INDEX

With MariaDB Xpand, the CREATE INDEX statement can be used to set index options when creating a new index on an existing table:

CREATE INDEX idx_invoices_branch_customer
   ON hq_sales.invoices (branch_id, customer_id)
   INVISIBLE;

CREATE TABLE

With MariaDB Xpand, the CREATE TABLE statement can be used to set index options when creating a new table with an index using the INDEX clause:

CREATE TABLE hq_sales.invoices (
   invoice_id BIGINT UNSIGNED AUTO_INCREMENT 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 (branch_id, customer_id) INVISIBLE
);

ALTER TABLE

With MariaDB Xpand, the ALTER TABLE statement can be used to set index options when modifying an existing index on an existing table using the ALTER INDEX clause:

ALTER TABLE hq_sales.invoices
   ALTER INDEX (branch_id, customer_id) INVISIBLE;

The ALTER TABLE statement can be used to set index options when creating a new index on an existing table using the ADD INDEX clause:

ALTER TABLE hq_sales.invoices
   ADD INDEX (branch_id, customer_id) INVISIBLE;

Executable Comments

With MariaDB Xpand, the CREATE TABLE statement allows some index options to be specified as executable comments in the following format:

/*$ executable comment */

The following example sets the DISTRIBUTE index option in an executable comment:

CREATE TABLE hq_sales.invoices (
   invoice_id BIGINT UNSIGNED AUTO_INCREMENT 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 (branch_id, customer_id) /*$ DISTRIBUTE=2 */
);

Accepted Index Options

MariaDB Xpand supports the following index options:

Index Option

Description

CONTAINER

Specifies whether the index's representation is in-memory or on-disk:

  • Set to SKIPLIST to create an in-memory representation

  • Set to LAYERED (default) to create an on-disk representation

DISTRIBUTE

Specifies the number of indexed columns to include in the index's distribution key

INVISIBLE

Specifies that the index is an invisible index

REPLICAS

Specifies the number of replicas Xpand maintains for each of the index's slices:

  • Set to a number to maintain a specific number of replicas (defaults to 2)

  • Set to ALLNODES to maintain a replica of each slice on every node

SLICES

Specifies the number of slices Xpand maintains for the index's representation

VISIBLE

Specifies that the index is not an invisible index

Ignored Index Options

MariaDB Xpand ignores the following index options to allow easy migrations from InnoDB:

  • KEY_BLOCK_SIZE

Unsupported Index Options

MariaDB Xpand does not support the following index options:

  • WITH PARSER

  • COMMENT

  • CLUSTERING