CREATE INDEX

CREATE INDEX

To create a secondary index:

CREATE INDEX idx_invoices_date_total
   ON hq_sales.invoices (invoice_date, invoice_total);

CREATE UNIQUE INDEX

To create a unique index:

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

CREATE COLUMNAR INDEX

To create a columnar index:

CREATE COLUMNAR INDEX idx_invoices_date_total
   ON hq_sales.invoices (invoice_date, invoice_total);

For best performance, it is recommended to load data into the table before adding a columnar index.

Index Options

Index options are used in CREATE INDEX statement to define index characteristics, for example, whether the index's representations are in-memory or on-disk, or the number of replicas MariaDB Xpand maintains for the index's slices.

The general syntax for setting index options is:

<OPTION_NAME> = <OPTION_VALUE>, [<OPTION_NAME> = <OPTION_VALUE> ...]

The equal sign (=) is optional.

For additional information on index options accepted by MariaDB Xpand and options supported just for compatibility purposes, see "Index Options with MariaDB Xpand".

CONTAINER Index Option

To specify that the index should reside in memory:

CREATE INDEX idx_invoices_branch_customer
   ON hq_sales.invoices (branch_id, customer_id)
      CONTAINER = SKIPLIST;

To specify that the index should reside on disk:

CREATE INDEX idx_invoices_branch_customer
   ON hq_sales.invoices (branch_id, customer_id)
      CONTAINER = LAYERED;

If you omit the CONTAINER option, by default, the index resides on disk.

DISTRIBUTE Index Option

The DISTRIBUTE index option specifies the number of indexed columns to include in the index's distribution key.

This statement creates an index for the invoices table with two indexed columns included in the index's distribution key:

CREATE INDEX idx_invoices_branch_customer
   ON hq_sales.invoices (branch_id, customer_id)
      DISTRIBUTE = 2;

For additional information, see "DISTRIBUTE Table Option for MariaDB Xpand".

INVISIBLE and VISIBLE Index Options

To create an invisible index:

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

To explicitly specify that the index is not invisible:

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

If you do not specify visibility for an index, it will be visible by default.

For additional information, see "INVISIBLE Indexes with MariaDB Xpand".

SLICES Index Option

The SLICES index option sets the number of initial slices for the index. Slices are distributed throughout the cluster to facilitate evenly distributed query processing.

This statement creates a sliced index for the invoices table:

CREATE INDEX idx_invoices_branch_customer
   ON hq_sales.invoices (branch_id, customer_id)
      SLICES = 3;

For additional information, see "SLICES Table Option for MariaDB Xpand".

REPLICAS Index Option

The REPLICAS index option configures the number of replicas that are maintained for the index. MariaDB Xpand maintains multiple replicas of each slice to provide fault tolerance and high availability, and to ensure reads are balanced. By default, 2 replicas are maintained for each slice.

This statement creates an index with 3 replicas:

CREATE INDEX idx_invoices_branch_customer
   ON hq_sales.invoices (branch_id, customer_id)
      REPLICAS = 3;

To maintain a complete copy of the index on every node, use REPLICAS = ALLNODES:

CREATE INDEX idx_invoices_branch_customer
   ON hq_sales.invoices (branch_id, customer_id)
      REPLICAS = ALLNODES;

As a result, every slice in the idx_invoices_branch_customer index will have a replica stored on every node of the MariaDB Xpand cluster.

For additional information, see "REPLICAS Table Option for MariaDB Xpand".

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