In this blog post, we’ll look at some of the basic housekeeping steps for MySQL indexes.
We all know that indexes can be the difference between a high-performance database and a bad/slow/painful query ride. It’s a critical part that needs deserves some housekeeping once in a while. So, what should you check? In no particular order, here are some things to look at:
1. Unused indexes
With sys schema, is pretty easy to find unused indexes: use the schema_unused_indexes view.
1 2 3 4 5 6 7 8 | mysql> select * from sys.schema_unused_indexes; +---------------+-----------------+-------------+ | object_schema | object_name | index_name | +---------------+-----------------+-------------+ | world | City | CountryCode | | world | CountryLanguage | CountryCode | +---------------+-----------------+-------------+ 2 rows in set (0.01 sec) |
This view is based on the performance_schema.table_io_waits_summary_by_index_usage table, which will require enabling the Performance Schema, the events_waits_current consumer and the wait/io/table/sql/handler instrument. PRIMARY (key) indexes are ignored.
If you don’t have them enabled, just execute these queries:
1 2 | update performance_schema.setup_consumers set enabled = 'yes' where name = 'events_waits_current'; update performance_schema.setup_instruments set enabled = 'yes' where name = 'wait/io/table/sql/handler'; |
Quoting the documentation:
“To trust whether the data from this view is representative of your workload, you should ensure that the server has been up for a representative amount of time before using it.”
And by representative amount, I mean representative:
- Do you have a weekly job? Wait at least one week
- Do you have monthly reports? Wait at least one month
- Don’t rush!
Once you’ve found unused indexes, remove them.
2. Duplicated indexes
You have two options here:
- pt-duplicate-key-checker
- the schema_redundant_indexes view from sys_schema
The pt-duplicate-key-checker is part of Percona Toolkit. The basic usage is pretty straightforward:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 | [root@e51d333b1fbe mysql-sys]# pt-duplicate-key-checker # ######################################################################## # world.CountryLanguage # ######################################################################## # CountryCode is a left-prefix of PRIMARY # Key definitions: # KEY `CountryCode` (`CountryCode`), # PRIMARY KEY (`CountryCode`,`Language`), # Column types: # `countrycode` char(3) not null default '' # `language` char(30) not null default '' # To remove this duplicate index, execute: ALTER TABLE `world`.`CountryLanguage` DROP INDEX `CountryCode`; # ######################################################################## # Summary of indexes # ######################################################################## # Size Duplicate Indexes 2952 # Total Duplicate Indexes 1 # Total Indexes 37 |
Now, the schema_redundant_indexes view is also easy to use once you have sys schema installed. The difference is that it is based on the information_schema.statistics table:
1 2 3 4 5 6 7 8 9 10 11 12 13 | mysql> select * from schema_redundant_indexesG *************************** 1. row *************************** table_schema: world table_name: CountryLanguage redundant_index_name: CountryCode redundant_index_columns: CountryCode redundant_index_non_unique: 1 dominant_index_name: PRIMARY dominant_index_columns: CountryCode,Language dominant_index_non_unique: 0 subpart_exists: 0 sql_drop_index: ALTER TABLE `world`.`CountryLanguage` DROP INDEX `CountryCode` 1 row in set (0.00 sec) |
Again, once you find the redundant index, remove it.
3. Potentially missing indexes
The statements summary tables from the performance schema have several interesting fields. For our case, two of them are pretty important: NO_INDEX_USED (means that the statement performed a table scan without using an index) and NO_GOOD_INDEX_USED (“1” if the server found no good index to use for the statement, “0” otherwise).
Sys schema has one view that is based on the performance_schema.events_statements_summary_by_digest table, and is useful for this purpose: statements_with_full_table_scans, which lists all normalized statements that have done a table scan.
For example:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 | mysql> select * from world.CountryLanguage where isOfficial = 'F'; 55a208785be7a5beca68b147c58fe634 - 746 rows in set (0.00 sec) mysql> select * from statements_with_full_table_scansG *************************** 1. row *************************** query: SELECT * FROM `world` . `Count ... guage` WHERE `isOfficial` = ? db: world exec_count: 1 total_latency: 739.87 us no_index_used_count: 1 no_good_index_used_count: 0 no_index_used_pct: 100 rows_sent: 746 rows_examined: 984 rows_sent_avg: 746 rows_examined_avg: 984 first_seen: 2016-09-05 19:51:31 last_seen: 2016-09-05 19:51:31 digest: aa637cf0867616c591251fac39e23261 1 row in set (0.01 sec) |
The above query doesn’t use an index because there was no good index to use, and thus was reported. See the explain output:
1 2 3 4 5 6 7 8 9 10 11 12 | mysql> explain select * from world.CountryLanguage where isOfficial = 'F'G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: CountryLanguage type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 984 Extra: Using where |
Note that the “query” field reports the query digest (more like a fingerprint) instead of the actual query.
In this case, the CountryLanguage table is missing an index over the “isOfficial” field. It is your job to decide whether it is worth it to add the index or not.
4. Multiple column indexes order
It was explained before that Multiple Column index beats Index Merge in all cases when such index can be used, even when sometimes you might have to use index hints to make it work.
But when using them, don’t forget that the order matters. MySQL will only use a multi-column index if at least one value is specified for the first column in the index.
For example, consider this table:
1 2 3 4 5 6 7 8 9 10 11 12 | mysql> show create table CountryLanguageG *************************** 1. row *************************** Table: CountryLanguage Create Table: CREATE TABLE `CountryLanguage` ( `CountryCode` char(3) NOT NULL DEFAULT '', `Language` char(30) NOT NULL DEFAULT '', `IsOfficial` enum('T','F') NOT NULL DEFAULT 'F', `Percentage` float(4,1) NOT NULL DEFAULT '0.0', PRIMARY KEY (`CountryCode`,`Language`), KEY `CountryCode` (`CountryCode`), CONSTRAINT `countryLanguage_ibfk_1` FOREIGN KEY (`CountryCode`) REFERENCES `Country` (`Code`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
A query against the field “Language” won’t use an index:
1 2 3 4 5 6 7 8 9 10 11 12 | mysql> explain select * from CountryLanguage where Language = 'English'G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: CountryLanguage type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 984 Extra: Using where |
Simply because it is not the leftmost prefix for the Primary Key. If we add the “CountryCode” field, now the index will be used:
1 2 3 4 5 6 7 8 9 10 11 12 | mysql> explain select * from CountryLanguage where Language = 'English' and CountryCode = 'CAN'G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: CountryLanguage type: const possible_keys: PRIMARY,CountryCode key: PRIMARY key_len: 33 ref: const,const rows: 1 Extra: NULL |
Now, you’ll have to also consider the selectivity of the fields involved. Which is the preferred order?
In this case, the “Language” field has a higher selectivity than “CountryCode”:
1 2 3 4 5 6 | mysql> select count(distinct CountryCode)/count(*), count(distinct Language)/count(*) from CountryLanguage; +--------------------------------------+-----------------------------------+ | count(distinct CountryCode)/count(*) | count(distinct Language)/count(*) | +--------------------------------------+-----------------------------------+ | 0.2368 | 0.4644 | +--------------------------------------+-----------------------------------+ |
So in this case, if we create a multi-column index, the preferred order will be (Language, CountryCode).
Placing the most selective columns first is a good idea when there is no sorting or grouping to consider, and thus the purpose of the index is only to optimize where lookups. You might need to choose the column order, so that it’s as selective as possible for the queries that you’ll run most.
Now, is this good enough? Not really. What about special cases where the table doesn’t have an even distribution? When a single value is present way more times than all the others? In that case, no index will be good enough. Be careful not to assume that average-case performance is representative of special-case performance. Special cases can wreck performance for the whole application.
In conclusion, we depend heavily on proper indexes. Give them some love and care once in a while, and the database will be very grateful.
All the examples were done with the following MySQL and Sys Schema version:
1 2 3 4 5 6 | mysql> select * from sys.version; +-------------+-----------------+ | sys_version | mysql_version | +-------------+-----------------+ | 1.5.1 | 5.6.31-77.0-log | +-------------+-----------------+ |
Hi Daniel,
Good balanced discussion on how often to do house keeping 🙂
I just wanted to mention one thing – be careful when dropping seemingly redundant indexes. If a hint is used on a query, you will get an error rather than an alternative index selected.
As mentioned in Manyi’s interview last week – in MySQL 8.0 we will support invisible indexes for this purpose (hide first, then delete after some time). This will also help for case #1 that you mention.
– Morgan
Hello and thanks for the nice post. The ways written here are good especially for DBAs who don’t know well about the app using a database.
I translated this post into Japanese https://yakst.com/ja/posts/4236
Please let me know if it’s a problem.
Thanks again!
P.S. I also translated Morgan’s comment as an additional note 🙂