INVISIBLE Indexes with MariaDB Xpand

Overview

MariaDB Xpand supports invisible indexes:

  • When an index is invisible, Xpand does not automatically use the index for queries

  • Invisible indexes can be used for queries by specifying USE INDEX or FORCE INDEX

  • Invisible indexes can be used to test whether an existing index can be removed

  • Invisible indexes can be used to add a new index without impacting queries

Compatibility

  • MariaDB Xpand 5.3

  • MariaDB Xpand 6.0

  • MariaDB Xpand 6.1

Why Use an Invisible Index?

Identifying an index as INVISIBLE allows the user to evaluate the impact of:

  • Dropping an existing index before actually doing so.

  • Evaluating a new index without impacting existing queries.

Dropping and re-adding an index can take substantial time for large tables, whereas marking an index as VISIBLE or INVISIBLE is a fast, in-place operation that can be easily undone if you do not achieve the expected results.

Invisible Index Characteristics

  • INVISIBLE indexes are ignored by Sierra, Xpand's query planner, but continue to be maintained such that they are usable when explicitly named or made VISIBLE.

  • Unique INVISIBLE indexes still prevent duplicate keys.

  • INVISIBLE indexes are allowed on persistent tables, partitioned tables, In-Memory tables, and temporary tables.

  • Any index, with the exception primary keys, may be made INVISIBLE.

  • If FORCE INDEX or USE INDEX is used with an INVISIBLE index, Xpand will run the query using the index hint. MariaDB issues an error.

CREATE INDEX

With MariaDB Xpand, the CREATE INDEX statement can be used to create a new invisible index on an existing table by specifying the INVISIBLE index option:

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

DROP INDEX

With MariaDB Xpand, the DROP INDEX statement can be used to drop an invisible 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 an invisible index by specifying the INVISIBLE index option:

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

ALTER TABLE

ALTER TABLE .. ADD INDEX .. INVISIBLE

With MariaDB Xpand, the ALTER TABLE statement can be used to create a new invisible index on an existing table by specifying the INVISIBLE index option:

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

ALTER TABLE .. ALTER INDEX .. INVISIBLE

With MariaDB Xpand, the ALTER TABLE statement can be used to make an index invisible by specifying the INVISIBLE index option:

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

ALTER TABLE .. ALTER INDEX .. VISIBLE

With MariaDB Xpand, the ALTER TABLE statement can be used to make an index visible by specifying the VISIBLE index option:

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

ALTER TABLE .. DROP INDEX

With MariaDB Xpand, the ALTER TABLE statement can be used to drop an invisible 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 an invisible index using the RENAME INDEX clause:

ALTER TABLE hq_sales.invoices
   RENAME INDEX idx_invoices_branch_customer
   TO idx_invoices_branch_customer_old;

Sample Scenarios

Steps for evaluating an INVISIBLE index vary depending on whether or not the index already exists.

Make an Existing Index INVISIBLE

ALTER the index to be INVISIBLE then evaluate the impact:

  1. Do queries appear in the query.log as SLOW that did not before? If so, the existing index may be beneficial and not a candidate for removal. For additional information, see "Query Logging for MariaDB Xpand".

  2. Does the EXPLAIN for queries that previously used the INVISIBLE index now include any full table scans? This may indicate that the INVISIBLE index was relevant and should not be removed. For additional information, see "EXPLAIN for MariaDB Xpand".

  3. Is the INVISIBLE index specifically named by any USE INDEX or FORCE INDEX hints? These will generate an error if the index is permanently removed.

DROP the index or ALTER it to be VISIBLE, depending on the results. Continue to monitor the query.log.

Creating a New INVISIBLE Index

CREATE a new index as INVISIBLE.

  1. Introduce the new index into queries by specifying USE INDEX or FORCE INDEX.

  2. Compare EXPLAIN output with and without the index to determine if the new index will improve performance. For additional information, see "EXPLAIN for MariaDB Xpand".

  3. Test performance of queries with and without the index by specifying USE INDEX or FORCE INDEX.

DROP the index or ALTER it to be VISIBLE, depending on the results. Continue to monitor the query.log.