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.

Compatibility

  • MariaDB Enterprise Server 10.2

  • MariaDB Enterprise Server 10.3

  • MariaDB Enterprise Server 10.4

  • MariaDB Enterprise Server 10.5

  • 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)
   INVISIBLE;

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) INVISIBLE
);

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:

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;

Accepted Index Options

MariaDB Enterprise Server supports the following index options:

Index Option

Description

INVISIBLE

Specifies that the index is an invisible index

VISIBLE

Specifies that the index is not an invisible index