Index Options with MariaDB Enterprise Server

Overview

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

The IGNORED index option specifies that the index is visible and maintained, but not used by the optimizer.

The NOT IGNORED option specifies that the index should not be ignored by the optimizer.

Compatibility

  • MariaDB Enterprise Server 10.6

CREATE INDEX

With MariaDB Enterprise Server, 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)
   IGNORED;

CREATE TABLE

With MariaDB Enterprise Server, 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) IGNORED
);

ALTER TABLE

With MariaDB Enterprise Server, 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:

CREATE OR REPLACE TABLE t1 (id INT PRIMARY KEY, a INT, KEY k1(a) IGNORED);
EXPLAIN SELECT * FROM t1 ORDER BY a;

+------+-------------+-------+------+---------------+------+---------+------+------+----------------+
| id   | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra          |
+------+-------------+-------+------+---------------+------+---------+------+------+----------------+
|    1 | SIMPLE      | t1    | ALL  | NULL          | NULL | NULL    | NULL | 1    | Using filesort |
+------+-------------+-------+------+---------------+------+---------+------+------+----------------+

ALTER TABLE t1 ALTER INDEX k1 NOT IGNORED;
EXPLAIN SELECT * FROM t1 ORDER BY a;

+------+-------------+-------+-------+---------------+------+---------+------+------+-------------+
| id   | select_type | table | type  | possible_keys | key  | key_len | ref  | rows | Extra       |
+------+-------------+-------+-------+---------------+------+---------+------+------+-------------+
|    1 | SIMPLE      | t1    | index | NULL          | k1   | 5       | NULL | 1    | Using index |
+------+-------------+-------+-------+---------------+------+---------+------+------+-------------+

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 t1 ADD INDEX k2(a) IGNORED;

Accepted Index Options

MariaDB Enterprise Server supports the following index options:

Index Option

Description

IGNORED

Specifies that the index is visible and maintained, but not used by the optimizer

NOT IGNORED (default)

Specifies that the index should not be ignored by the optimizer