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 the 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 be 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 are 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.
Download MariaDB Server
What’s New in MariaDB Enterprise X6
Create an Index with InnoDB
