Maria DB's New Feature: IGNORED Indexes
MariaDB Corporation released versions of MariaDB Enterprise Server 10.6 and MariaDB Community Server 10.6 with many useful new features including IGNORED indexes.
·
Join the DZone community and get the full member experience.
Join For FreeIn 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 a 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 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.
Published at DZone with permission of Pon Suresh Pandian. See the original article here.
Opinions expressed by DZone contributors are their own.
Comments