MariaDB Xpand Indexes

Columns on MariaDB Xpand tables can be indexed. The optimizer can use indexes for certain types of queries to improve query performance.

MariaDB Xpand Index Compatibility

The Xpand Performance topology and Xpand Storage Engine topology are compatible with the following index features:

Index Feature

Xpand Performance

Xpand Storage Engine

Primary Keys

Yes

Yes

Secondary Indexes

Yes

Yes

Unique Indexes

Yes

Yes

Invisible Indexes

Yes

No

Composite (Multi-Column) Indexes

Yes

Yes

Prefix Indexes

Yes

Yes

Columnar Indexes

Yes (Xpand 6)

No

For additional information, see "Xpand Architecture".

Defining Indexes with MariaDB Xpand

For details on how to define indexes with MariaDB Xpand, choose an index type:

Primary Keys

The Primary Key index for a table is used to uniquely identify every row.

Secondary Indexes

Secondary indexes are used to improve query performance.

Unique Indexes

Unique indexes are used to enforce unique constraints for the indexed column(s).

Columnar Indexes

Columnar indexes are used to perform analytic style queries on row-based data.

Examples

Create a MariaDB Xpand Table with a Primary Key

To create an Xpand table with a primary key, use the CREATE TABLE statement with the PRIMARY KEY 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)
);

Note

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

Add a Primary Key to an MariaDB Xpand Table

To add a primary key to an Xpand table, use the ALTER TABLE statement with the ADD PRIMARY KEY clause:

ALTER TABLE hq_sales.invoices ADD PRIMARY KEY (invoice_id);

Create a MariaDB Xpand Table with a Secondary Index

To create an Xpand 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.

Add a Secondary Index to a MariaDB Xpand Table

To add a secondary index to an Xpand table, use the ALTER TABLE statement with the ADD INDEX clause:

ALTER TABLE hq_sales.invoices ADD INDEX (invoice_date);

Create a MariaDB Xpand Table with a Unique Index

To create an Xpand table with a unique index, use the CREATE TABLE statement with the UNIQUE 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),
   UNIQUE INDEX (customer_email)
);

Note

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

Add a Unique Index to a MariaDB Xpand Table

To add a unique index to an Xpand table, use the ALTER TABLE statement with the ADD UNIQUE INDEX clause:

ALTER TABLE hq_sales.customers ADD UNIQUE INDEX (customer_email);

Create a MariaDB Xpand Table with a Composite Index

To create an Xpand table with a composite index, use the CREATE TABLE statement with the INDEX (col1, ..., colN) 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)
);

Note

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

Create a MariaDB Xpand Table with a Prefix Index

To create an Xpand table with a prefix index, use the CREATE TABLE statement with the INDEX (col(prefix_length)) 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))
);

Note

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

Create a MariaDB Xpand Table with a Columnar Index

To create an Xpand table with a columnar index, use the CREATE TABLE statement with the COLUMNAR 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),
   COLUMNAR INDEX cols (invoice_date, invoice_total)
);

Note that there is only one columnar index allowed per table.

Add a Columnar Index to a MariaDB Xpand Table

To add a columnar index to an Xpand table, use the CREATE COLUMNAR INDEX statement:

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

FAQ

What columns can be indexed with Xpand?

With Xpand, indexes can include one or more columns:

Type

Description

Single Column Indexes

  • An index on a single column.

  • All index types support column indexes.

Composite Indexes (Multi-Column)

  • An index on multiple columns.

  • All index types except spatial indexes support composite indexes, but Xpand does not support spatial indexes.

What data types can be indexed with Xpand?

Xpand supports indexes on all supported data types, but some data types may be limited to prefix indexes.

Xpand only supports prefix indexes for the following data types:

What part of the column's value can be indexed with Xpand?

With Xpand, indexes can include the full column value or only a prefix of the column value:

Column Part

Description

Full Value

  • The full value of the column is indexed.

  • For example, if a VARCHAR column can store 500 characters, then all 500 characters can be indexed for each row.

  • Full values cannot be indexed for some data types.

  • If a full column value cannot be indexed, then Xpand will automatically index the maximum prefix length for the column instead.

Prefix

  • A specific prefix length of the column is indexed.

  • For example, if a VARCHAR column can store 500 characters, but the column's prefix length for the index is set to 100, then only the first 100 characters can be indexed for each row.

What index options are supported with Xpand?

Xpand accepts but ignores the following index options:

  • KEY_BLOCK_SIZE

Xpand does not support the following index options:

  • WITH PARSER

  • COMMENT

  • CLUSTERING