Last updated on 11 Luglio 2021
MariaDB introduced Ignored Indexes (KnowledgeBase) in version 10.6. The feature is substantially very similar to MySQL Invisible Indexes (docs), introduced in version 8.0. Here we’ll see how to use Ignored Indexes drop indexes in production with a reasonable risk.
See our MariaDB review for other features.
As explained in Finding Duplicate Indexes and Unused Indexes in MariaDB/MySQL, it’s wise to periodically drop duplicate indexes and unused indexes.
But if you have queries using one of the following syntaxes, once the index is dropped they will start to fail with an error:
tab_name IGNORE INDEX (...) tab_name USE INDEX (...) tab_name FORCE INDEX (...)
(This syntax can be used whenever a table is mentioned in a query. For the complete syntax and the use cases, see the documentation)
If this happens, you will want to recreate the index immediately. But creating an index takes time, especially big indexes. Even dropping a big index can cause intensive flushing to the disk in some cases, so you don’t want to do it for nothing.
But fortunately, making an index
is a fast operation: it’s just a matter of changing a flag inde the index metadata. While the index is
it cannot be used by the queries, and queries that mention that index using the above syntaxes will fail with an error.
So, the plan is simple:
- Make an index
- Wait and periodically check if some queries are failing;
- If so, make the index
NOT IGNOREDand fix the queries before retrying;
- If no query fails for a pre-determined period of time, drop the index.
A similar (not identical) procedure was described in Dropping database objects in a safe way. Make sure to take a look at that article.
Ignored indexes limitations
Limitations I’ve found so far:
- Primary keys cannot be ignored (as expected);
UNIQUEindexes can be ignored, but trying to duplicate a value will still generate an error (as expected).
INVISIBLEkeyword will not work on MariaDB (and
[NOT] IGNOREDwill not work on MySQL).
To be able to follow the procedure explained here you need to set
. In MariaDB the default is 0, for some reason. If you have the
disabled, enabling it will require a MariaDB restart.
MariaDB allows to declare indexes as
, for MySQL compatibility:
CREATE OR REPLACE TABLE employee ( id INTEGER UNSIGNED AUTO_INCREMENT, name VARCHAR(50) NOT NULL, surname VARCHAR(50) NOT NULL, email VARCHAR(50) NOT NULL, UNIQUE unq_email (email), INDEX idx_surname (surname) VISIBLE, INDEX idx_surname_name (surname, name) VISIBLE, PRIMARY KEY (id) );
As you can see, the
is a duplicate (actually it’s a prefix) of
. For this reason we want to drop it.
But, as illustrated above, we’ll start by making it invisible:
ALTER TABLE employee ALTER INDEX idx_surname IGNORED;
Now we need to know if any query fails with an error because it mentions that index. We can do this by querying the
table. Let’s run a query like this at regular intervals:
SELECT DIGEST_TEXT FROM performance_schema.events_statements_summary_by_digest WHERE DIGEST_TEXT LIKE '%`employee`%INDEX%(%`idx_surname`%)%' AND SUM_ERRORS > 0 AND LAST_SEEN > (NOW() - INTERVAL 10 MINUTE) ;
We look for queries that generate errors (
SUM_ERRORS > 0
) and were run after the index was made
LAST_SEEN > (NOW() - INTERVAL 10 MINUTE)
The condition on
(the normalised query text) is specific enough to avoid catching unrelated syntaxes, but generic enough to catch all the syntax mentioned above. Let’s not dig into these details though.
Suppose we get the following result:
*************************** 1. row *************************** DIGEST_TEXT: SELECT * FROM `employee` FORCE INDEX ( `idx_surname` ) WHERE `surname` = ? AND NAME = ? 1 row in set (0.004 sec)
This digest actually represents this query:
SELECT * FROM employee FORCE INDEX (idx_surname) WHERE surname = 'Baker' AND name = 'Tom';
We’ll need to fix this query by removing
or by changing the index name to
. But we’ll do it at a later time. For the time being, we need to act fast to heal production:
ALTER TABLE employee ALTER INDEX idx_surname NOT IGNORED;
Ignored indexes show up as
SHOW CREATE TABLE
MariaDB [test]> SHOW CREATE TABLE employee \G *************************** 1. row *************************** Table: employee Create Table: CREATE TABLE `employee` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `name` varchar(50) NOT NULL, `surname` varchar(50) NOT NULL, `email` varchar(50) NOT NULL, PRIMARY KEY (`id`), UNIQUE KEY `unq_email` (`email`), KEY `idx_surname` (`surname`) IGNORED, KEY `idx_surname_name` (`surname`,`name`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 1 row in set (0.008 sec)
To obtain a list of ignored indexes:
SELECT DISTINCT TABLE_SCHEMA, TABLE_NAME, INDEX_NAME FROM information_schema.STATISTICS WHERE IGNORED = 'YES' ;
- A summary of MariaDB 10.6
- Dropping database objects in a safe way
- Finding Duplicate Indexes and Unused Indexes in MariaDB/MySQL
We discussed how to use ignored indexes to make index dropping safer in production. If you have questions or ideas to share, please leave a comment below.