Ignored Indexes
In August, MariaDB Corporation released major versions of MariaDB Enterprise Server 10.6 and MariaDB Community Server 10.6 which include useful new features. One of these new features is IGNORED
indexes. The ability to ignore indexes makes it easier to determine if specific indexes are useful for improving query performance.
We use indexes to improve query performance in transactional and OLTP workloads. But, sometimes an index can become redundant due to schema changes that modify table structure, application upgrades that modify database queries, MariaDB upgrades that implement new query execution optimizations, or changes to the table’s data that result in different optimizations being chosen for query execution.
Without IGNORED
indexes, it could be difficult to test if an index was still useful. An index could be dropped very quickly with minimal usage of system resources. But if testing showed that the index was still useful, the index would have to be re-created. Re-creating an index would require a full table scan, which can require a lot of I/O for large tables.
When an index is given the IGNORED
option, the query optimizer will not choose to use the index for query execution during query planning. Ignoring an index can be used to determine if the index is still useful.
An index can be ignored by executing ALTER TABLE .. ALTER INDEX .. IGNORED
. The operation does not remove the index. Instead, it only changes the index’s metadata which requires very little system resources. And, the change can be easily reversed using ALTER TABLE .. ALTER INDEX .. NOT IGNORED
. Once you understand the consequences, you can decide whether to drop the index or not.
What are indexes in RDBMS?
First, a quick refresher. Indexing is a data structure technique. It can improve the performance of query evaluation by reducing the number of physical pages the database must access to find the rows that meet a specific condition (i.e. WHERE
clause, JOIN
key, foreign key constraints, etc.). Indexing can also improve the performance of sorting query results, because InnoDB’s B+Tree indexes store values in sorted order, so the final merge sort can sometimes be skipped when an index is used.
Performance is always a concern when administering databases. And, slow queries are a frequent cause of problems. DBAs are always looking to identify and fix problem queries. Indexing is a standard solution for speeding up slow queries, but creating and maintaining an index has its own cost and it is costlier on very large tables than on smaller tables.
Whenever you create an index with InnoDB, copies of the indexed columns and the primary key are created on disk. If the index is used frequently, InnoDB caches the index pages in the Buffer Pool in memory. So adding an index to a large table requires a full table scan, which is an expensive operation. Whenever an indexed column is updated, the indexed data must also be updated. The index maintenance can require significant overhead for large tables that are updated frequently. This is where IGNORED
indexes come into the picture, since it enables you to decide if an index is providing value and is worth maintaining.
What are ignored indexes?
IGNORED
indexes are visible and maintained, but are not used by the optimizer. (MySQL 8 has a similar feature which they call “invisible indexes”.) The IGNORED
index option can be used with existing indexes or can be used when creating a new index.
To ignore a new or existing index, use the IGNORED
index option in the index definition with the CREATE TABLE
, CREATE INDEX
, or ALTER TABLE
statements.
How do you Ignore an index on an existing table?
By default, indexes are NOT IGNORED
and can be used by the optimizer. An existing index can be marked as IGNORED
or NOT IGNORED
with an ALTER TABLE .. ALTER INDEX
statement.
Ignore an IndexÂ
MariaDB [sbtest]> ALTER TABLE sbtest9 ALTER INDEX k_9 IGNORED; Query OK, 0 rows affected (0.006 sec) Records: 0Â Duplicates: 0Â Warnings: 0
MariaDB [sbtest]> SHOW CREATE TABLE sbtest9\G
*************************** 1. row ***************************
       Table: sbtest9
Create Table: CREATE TABLE `sbtest9` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `k` int(11) NOT NULL DEFAULT 0,
  `c` char(120) NOT NULL DEFAULT '',
  `pad` char(60) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`),
  KEY `k_9` (`k`) IGNORED
) ENGINE=InnoDB AUTO_INCREMENT=2113842 DEFAULT CHARSET=latin1
1 row in set (0.001 sec)
Primary Key
The primary key index can’t be IGNORED
. If there is no primary key in a table but it has a UNIQUE
key, the first UNIQUE
key is treated as the primary key, so it can’t be IGNORED
.
MariaDB [flims]> ALTER TABLE store ALTER INDEX idx_unique_manager IGNORED; ERROR 4174 (HY000): A primary key cannot be marked as IGNORE
Not Ignore an Index
Re-enabling an IGNORED
index is straightforward. An IGNORED
index can be marked as NOT IGNORED
with an ALTER TABLE .. ALTER INDEX
statement.
MariaDB [flims]> ALTER TABLE rental ALTER INDEX idx_combo NOT IGNORED; Query OK, 0 rows affected (0.006 sec) Records: 0Â Duplicates: 0Â Warnings: 0
Use Cases
Dropping an index can be risky. If testing shows that some queries still need the index, re-creating it is a much more expensive operation than dropping it. Re-creating the index could take a while and use a lot of system resources. Instead, altering the index to be IGNORED
lets us explore if an index is useful without having to drop and potentially recreate it.
As DBAs, we don’t always know the full set of queries used by the application. We can use an IGNORED
index in a staging environment to evaluate if the application explicitly chooses the index using index hints like USE INDEX
or FORCE INDEX
. If the application chooses the IGNORED
index with an index hint, MariaDB will throw an error.
Ignoring an index is also helpful in cases where dropping an index improves performance of specific queries. If those queries aren’t executed very often, it might be worthwhile to temporarily ignore the index, run the specific query, and then re-enable the index. This is a great case for using IGNORED
indexes.
Use sys.schema_unused_indexes
Another new feature in MariaDB Enterprise Server 10.6 and Community Server 10.6, is sys schema
. With sys schema
we can easily find unused indexes. This information can be used with the IGNORED
index feature to evaluate whether an index is useful.
For example, when we check the sys.schema_unused_indexes
for a specific table sbtest9
, it shows five (5) unused indexes.
MariaDB [sbtest]> SELECT * FROM sys.schema_unused_indexes WHERE object_name='sbtest9'; +---------------+-------------+---------------+ | object_schema | object_name | index_name  | +---------------+-------------+---------------+ | sbtest    | sbtest9   | k_9      | | sbtest    | sbtest9   | idx_c     | | sbtest    | sbtest9   | idx_cmobo   | | sbtest    | sbtest9   | idx_cmobo_pad | | sbtest    | sbtest9   | idx_cmobo_k  | +---------------+-------------+---------------+ 5 rows in set (0.001 sec)
However, it might not be ideal to immediately delete indexes found with sys.schema_unused_indexes
, because there is a chance that an index is needed by a query that is only executed infrequently. In that case, the safer option is to temporarily set the index to IGNORED
and perform more thorough testing. If testing shows that the index is not used, then the index can be completely removed.
Check Index Usage for Specific Queries
As a DBA, I may not be sure which index will be used by a specific query. I can use EXPLAIN
to look at the query plan. This can be helpful to determine if an index is used for a query, and IGNORED
indexes can be used to check if the query would perform worse without the index.
For example, the following shows the EXPLAIN
output for a query:
MariaDB [sbtest]> EXPLAIN SELECT a.id,a.pad,b.total_time FROM sbtest9 a JOIN sbtest15 b ON b.id=a.id WHERE a.k=1000704 AND b.pad LIKE '587681%'; +------+-------------+-------+--------+-----------------------------------+-------------+---------+-------------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+-------------+-------+--------+-----------------------------------+-------------+---------+-------------+------+-------------+ | 1 | SIMPLE | a | ref | PRIMARY,k_9,idx_cmobo,idx_cmobo_k | idx_cmobo_k | 4 | const | 107 | Using index | | 1 | SIMPLE | b | eq_ref | PRIMARY | PRIMARY | 4 | sbtest.a.id | 1 | Using where | +------+-------------+-------+--------+-----------------------------------+-------------+---------+-------------+------+-------------+ 2 rows in set (0.000 sec)
In this query, table “a” uses the idx_cmobo_k
index. We can set the index to IGNORED
to check if query performance would degrade if the index were dropped.
After I set the idx_cmobo_k
index to IGNORED
, I checked the EXPLAIN
plan for the query. I can see that the query is not using the IGNORED
index, but now the query is scanning a lot more rows (by a factor of approximately 18,000). This shows that the index is most likely useful for this specific query.
+------+-------------+-------+--------+---------------+---------+---------+-------------+---------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+-------+--------+---------------+---------+---------+-------------+---------+-------------+
| 1 | SIMPLE | a | ALL | PRIMARY | NULL | NULL | NULL | 1921969 | Using where |
| 1 | SIMPLE | b | eq_ref | PRIMARY | PRIMARY | 4 | sbtest.a.id | 1 | Using where |
+------+-------------+-------+--------+---------------+---------+---------+-------------+---------+-------------+
2 rows in set (0.000 sec)
Temporarily Ignore Indexes for Infrequent Queries
InnoDB tables often have many indexes used by multiple queries. In some cases, creating a new index for one query can result in worse query plans for unrelated queries. If the query that uses the new index is only executed infrequently, you can solve this problem by setting the index to IGNORED
mode when the index is not needed. When the index is needed again, you can change it back immediately with NOT IGNORED
.
For example, the following output shows the EXPLAIN
output for a query that uses the idx_combo
index:
MariaDB [flims]> EXPLAIN SELECT a.customer_id,a.return_date,b.rental_id,b.amount FROM rental a JOIN payment b ON a.rental_id=b.payment_id WHERE b.amount > 1.00 AND a.inventory_id=2079 AND b.payment_date > '2005-06-15 21:08:46'; +------+-------------+-------+--------+----------------------------------------+----------+---------+---------------------+-------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+-------------+-------+--------+----------------------------------------+----------+---------+---------------------+-------+-------------+ | 1 | SIMPLE | b | ref | PRIMARY,idx_combo,idx_fk_inventory_id | idx_combo| 30 | const | 120860 | Using where | | 1 | SIMPLE | a | eq_ref | PRIMARY | PRIMARY | 4 | sakila.b.payment_id | 1 | Using where | +------+-------------+-------+--------+----------------------------------------+----------+---------+---------------------+-------+-------------+
Despite using an index, this query reads a lot of rows. As a test, I IGNORED
the idx_combo
index.
MariaDB [flims]> ALTER TABLE rental ALTER INDEX idx_combo IGNORED; Query OK, 0 rows affected (0.005 sec) Records: 0 Duplicates: 0 Warnings: 0
After disabling the idx_combo
index, the optimizer is using the idx_fk_inventory_id
index and the number of rows being scanned is reduced (by a factor of approximately 24,000). If the idx_combo
index is only used for infrequently used queries, a reduction of this magnitude indicates that it may be worthwhile to leave the idx_combo
index ignored except when those queries are being executed.
MariaDB [flims]> EXPLAIN SELECT
a.customer_id,a.return_date,b.rental_id,b.amount FROM rental a JOIN
payment b ON a.rental_id=b.payment_id WHERE b.amount > 1.00 AND
a.inventory_id=2079 AND b.payment_date > '2005-06-15 21:08:46'\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: a
type: ref
possible_keys: PRIMARY,idx_fk_inventory_id
key: idx_fk_inventory_id
key_len: 3
ref: const
rows: 5
Extra:
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: b
type: eq_ref
possible_keys: PRIMARY,idx_combo
key: PRIMARY
key_len: 2
ref: sakila.a.rental_id
rows: 1
Extra: Using where
2 rows in set (0.000 sec)
FORCE INDEX() and USE INDEX() with an IGNORED Index
The optimizer treats IGNORED
indexes as if they don’t exist. They are not used in the query plans, or as a source of statistical information.
An attempt to use an IGNORED
index with a USE INDEX
or FORCE INDEX
throws an error 1176 (ER_KEY_DOES_NOT_EXISTS), which indicates that the index doesn’t exist.
MariaDB [flims]> EXPLAIN SELECT a.customer_id,a.return_date,b.rental_id,b.amount FROM rental a FORCE INDEX(idx_combo) JOIN payment b ON a.rental_id=b.payment_id WHERE b.amount > 1.00 AND a.inventory_id=2079 AND b.payment_date > '2005-06-15 21:08:46'\G ERROR 1176 (42000): Key 'idx_combo' doesn't exist in table 'a'
Find Ignored indexes using information_schema
We can find the number of IGNORED
indexes using the information_schema.statistics
table. In this table, the ignored
column will show whether indexes are IGNORED
or not.
MariaDB [flims]> SELECT table_schema,table_name,index_name,   column_name,ignored FROM information_schema.statistics WHERE IGNORED='yes'; +--------------+------------+--------------------+------------------+---------+ | table_schema | table_name | index_name     | column_name   | ignored | +--------------+------------+--------------------+------------------+---------+ | flims    | rental   | idx_combo     | staff_id     | YES   | | flims    | rental   | idx_combo     | inventory_id   | YES   | | flims    | store   | idx_unique_manager | manager_staff_id | YES   | +--------------+------------+--------------------+------------------+---------+ 3 rows in set (0.007 sec)
For More Information
The new IGNORED
indexes feature in MariaDB Enterprise Server 10.6 and MariaDB Community Server 10.6 is an extremely useful tool for easily exploring how indexes are being used without the risk or added work of dropping and recreating indexes.