Schedule Meeting

a

MariaDB Ignored Indexes

by | Jun 11, 2021 | MariaDB

Need Help?  Click Here for Expert Support

MariaDB introduced ignored Indexes 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.

MariaDB Ignored Index
Blixy is deliberately ignoring an index

The problem

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:

FROM table_name IGNORE INDEX (...)
FROM table_name USE INDEX (...)
FROM table_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 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.

So, the plan is simple:

  • Make an index IGNORED;
  • Wait and periodically check if some queries are failing;
  • If so, make the index NOT IGNORED and 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 and corner cases

Limitations and corner cases I’ve found so far:

  • Primary keys cannot be ignored;
  • UNIQUE indexes can be ignored, but trying to duplicate a value will still generate an error;
  • MySQL INVISIBLE indexes and MariaDB IGNORED indexes are equivalent features, but they use incompatible syntaxes.

Prerequisite

To be able to follow the procedure explained here you need to set performance_schema=1. In MariaDB the current default is 0, for some reason. If you have the performance_schema disabled, enabling it will require a MariaDB restart.

A complete, practical example

Consider this table:

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 idx_surname is a duplicate (actually it’s a prefix) of idx_surname_name. For this reason we want to drop it.

But, to make sure that we’re not going to cause problems in production, 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 events_statements_summary_by_digest table in the performance_schema. We can use this query:

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 IGNORED (LAST_SEEN > (NOW() - INTERVAL 10 MINUTE)).

The condition on DIGEST_TEXT (the normalised query text) is specific enough to avoid catching unrelated syntaxes, but generic enough to catch all variants of the syntax mentioned above.

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 FORCE INDEX or by changing the index name to idx_surname_name. 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;

Metadata

Ignored indexes show up as IGNORED in 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'
;

See also

Ebooks

Related articles

Conclusions

We discussed how to use ignored indexes to make index dropping safer in production.

If you need help with building proper indexes or run migrations in production, consider our Monthly DBA Time offer.

Federico Razzoli

All content in this blog is distributed under the CreativeCommons Attribution-ShareAlike 4.0 International license. You can use it for your needs and even modify it, but please refer to Vettabase and the author of the original post. Read more about the terms and conditions: https://creativecommons.org/licenses/by-sa/4.0/

About Federico Razzoli
Federico Razzoli is a database professional, with a preference for open source databases, who has been working with DBMSs since year 2000. In the past 20+ years, he served in a number of companies as a DBA, Database Engineer, Database Consultant and Software Developer. In 2016, Federico summarized his extensive experience with MariaDB in the “Mastering MariaDB” book published by Packt. Being an experienced database events speaker, Federico speaks at professional conferences and meetups and conducts database trainings. He is also a supporter and advocate of open source software. As the Director of Vettabase, Federico does business worldwide but prefers to do it from Scotland where he lives.

Recent Posts

Validating rows with CHECK constraints in MariaDB

Validating rows with CHECK constraints in MariaDB

Relational databases provide several ways to validate data. CHECK constraints are a powerful tool for in-database data validation. Their impact on performance is minimal, if any. In this article we'll discuss MariaDB support for CHECK constraints. Note that the CHECK...

MariaDB ColumnStore SQL limitations

MariaDB ColumnStore SQL limitations

MariaDB ColumnStore is an extremely fast and scalable solution for analytics built on MariaDB, which Vettabase supports. MariaDB ColumnStore inherits the MariaDB SQL dialect, and many MariaDB features. However, numerous MariaDB features are not available for the...

Installing MariaDB ColumnStore on Ubuntu, for production

Installing MariaDB ColumnStore on Ubuntu, for production

Let's see how to install MariaDB ColumnStore (single node) on Ubuntu. Let's also prepare the system to run ColumnStore with good performance, as we should always do in production. The following steps should work on any modern Ubuntu version up to and including 24.04...

Services

Need Help?  Click Here for Expert Support

0 Comments

Submit a Comment

Your email address will not be published. Required fields are marked *