MariaDB Ignored Indexes
MariaDB introduced Ignored Indexes in version 10.6. The feature is substantially very similar to MySQL Invisible Indexes, introduced in version 8.0. Here we’ll see how to use Ignored Indexes drop indexes in production with a reasonable risk.
As explained in Finding Duplicate Indexes and Unused Indexes in MariaDB/MySQL, it’s wise to periodically drop duplicate indexes and unused indexes.
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 IGNORED or NOT IGNORED is a fast operation: it’s just a matter of changing a flag inde the index metadata. While the index is IGNORED it cannot be used by the queries, and queries that mention that index using the above syntaxes will fail with an error.
A similar (not identical) procedure was described in Dropping database objects in a safe way. Make sure to take a look at that article.
