Secondary Indexes with MariaDB Xpand

This page is part of MariaDB's Documentation.

The parent of this page is: Indexes for MariaDB Xpand

Topics on this page:

Overview
Compatibility
CREATE INDEX
DROP INDEX
CREATE TABLE
ALTER TABLE

Overview

MariaDB Xpand supports row-based secondary indexes to speed up query execution:

Xpand supports single column and composite (multi-column) secondary indexes
Xpand generates a representation for each secondary index
Xpand stores secondary indexes as B+ trees, which are very efficient for searching for exact values, performing range scans, and checking uniqueness
Xpand creates replicas for each index's representation to guarantee fault tolerance

This page is about row-based secondary indexes in MariaDB Xpand. For details about Columnar indexes in MariaDB Xpand 6, see "MariaDB Xpand Columnar Indexes".

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 create a new secondary index on an existing table:

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

`DROP INDEX`

With MariaDB Xpand, the DROP INDEX statement can be used to drop a secondary index from an existing table:

DROP INDEX idx_invoices_branch_customer
   ON hq_sales.invoices;

`CREATE TABLE`

With MariaDB Xpand, the CREATE TABLE statement can be used to create a new table with a secondary 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 idx_invoices_branch_customer (branch_id, customer_id)
);

`ALTER TABLE`

`ALTER TABLE .. ADD INDEX`

With MariaDB Xpand, the ALTER TABLE statement can be used to create a new secondary index on an existing table using the ADD INDEX clause:

ALTER TABLE hq_sales.invoices
   ADD INDEX idx_invoices_date_total (invoice_date, invoice_total);

`ALTER TABLE .. ALTER INDEX`

With MariaDB Xpand, the ALTER TABLE statement can be used to alter an existing secondary index using the ALTER INDEX clause:

ALTER TABLE hq_sales.invoices
   ALTER INDEX idx_invoices_date_total (invoice_date, invoice_total) INVISIBLE;

`ALTER TABLE .. DROP INDEX`

With MariaDB Xpand, the ALTER TABLE statement can be used to drop a secondary index from an existing table using the DROP INDEX clause:

ALTER TABLE hq_sales.invoices
   DROP INDEX idx_invoices_branch_customer;

`ALTER TABLE .. RENAME INDEX`

With MariaDB Xpand, the ALTER TABLE statement can be used to rename a secondary index using the RENAME INDEX clause:

ALTER TABLE hq_sales.invoices
   RENAME INDEX idx_invoices_branch_customer
   TO idx_invoices_branch_customer_old;