Generate score for queries based on their indices usage
Closed this issue · 0 comments
macbre commented
From https://www.percona.com/files/presentations/WEBINAR-MySQL-Indexing-Best-Practices.pdf (slide 32).
Look at the “type” sorted from “good” to “bad”
– system,const,eq_ref,ref,range,index,ALL
• Note “rows” – higher numbers mean slower
query
• Check “key_len” – shows how many parts of the
key are really used
• Watch for Extra.
– Using Index - Good
– Using Filesort, Using Temporary - Bad
https://dev.mysql.com/doc/refman/5.7/en/using-explain.html
mysql> explain select max(season_nr) from title group by production_year;
+----+-------------+-------+-------+---------------+-----------------+---------+------+------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+-----------------+---------+------+------+--------------------------+
| 1 | SIMPLE | title | range | NULL | production_year | 5 | NULL | 201 | Using index for group-by |
+----+-------------+-------+-------+---------------+-----------------+---------+------+------+--------------------------+
1 row in set (0.01 sec)