How to interpret the output of MySQL EXPLAIN?

Question

I want to select the content of the column text from entrytable.

EXPLAIN SELECT text
FROM entrytable
WHERE user = 'username' &&
`status` = '1' && (
    `status_spam_user` = 'no_spam'
    || (
        `status_spam_user` = 'neutral' &&
        `status_spam_system` = 'neutral'
    )
)
ORDER BY datum DESC
LIMIT 6430 , 10

The table has three indices:

• index_user (user)
• index_datum (datum)
• index_status_mit_spam (status, status_spam_user, status_spam_system)

The EXPLAIN result is:

id  select_type     table       type    possible_keys                       key         key_len     ref     rows    Extra
1   SIMPLE          entrytable  ref     index_user,index_status_mit_spam    index_user  32          const   7800    Using where; Using filesort

• Is possible_keys the indices MySQL might want to use and keys the indices MySQL actually uses?
• Why is the index index_status_mit_spam not used? In the query, the colums have the same order as in the index,...
• Why is the index index_datum not used for the ORDER BY?
• How can I optimize my table-indices or the query? (The query above needs up to 3 seconds having about a million entries in the table)

Enquire

Answer 1

Answering your questions:

• Is possible_keys the indices MySQL might want to use and keys are the indices MySQL actually uses? Yes this is correct.

• Why is the index index_status_mit_spam not used? In the query, the columns have the same order as in the index. SQL uses statistics on the table's indexes to determine which index to use. The order of fields in the select statement has NO effect on which index to use. Statistics around indexes include information such as uniqueness of the index and other things. More unique indexes are likely to be used. Read more about this here: http://dev.mysql.com/doc/innodb/1.1/en/innodb-other-changes-statistics-estimation.html or here:http://dev.mysql.com/doc/refman/5.0/en//myisam-index-statistics.html. These factors determine how MySQL will select the one index to use. It will only use ONE index.

• Why is the index index_datum not used for the ORDER BY? MySQL will only use one index not two during a query as using a second index will slow down the query even more. Reading an index is NOT reading the table. This is related to operational efficiency of the query. Here is answers which can explain some concepts: https://dba.stackexchange.com/questions/18528/performance-difference-between-clustered-and-non-clustered-index/18531#18531 Or Adding limit clause to MySQL query slows it down dramatically Or MySQL indexes and when to group them. These answers have a lot detail which will help you understand MySQL Indexing.

• How can I optimize my table-indices or the query? (The query above needs up to 3 seconds having about a million entries in the table). Well there is a filesort here that is probably slowing you down. It might be that the table has too many indexes and MySQL is selecting the wrong one.

You need to understand that indexes speeds up reads and slows down writes to tables. So just adding indexes is not always a good idea. The above answers and pointers should help you gain a solid understanding.

Flower

Answer 2

• possible_keys denotes all the indices of your table (keys or index columns)
• MySQL optimizer decides the best way to EXECUTE a query, it may use any index (not necessary primary key), or none
• To force MySQL to use or ignore an index listed in the possible_keys column, use FORCE INDEX, USE INDEX, or IGNORE INDEX in your query

• Check this link - http://dev.mysql.com/doc/refman/5.1/en/index-hints.html .

  You can specify the scope of a index hint by adding a FOR clause to the hint. This provides more fine-grained control over the optimizer's selection of an execution plan for various phases of query processing. To affect only the indexes used when MySQL decides how to find rows in the table and how to process joins, use FOR JOIN. To influence index usage for sorting or grouping rows, use FOR ORDER BY or FOR GROUP BY. (However, if there is a covering index for the table and it is used to access the table, the optimizer will ignore IGNORE INDEX FOR {ORDER BY|GROUP BY} hints that disable that index.)

• Try forcing different index - check this link for sure - MySQL `FORCE INDEX` use cases?

• Understand EXPLAIN output format - http://dev.mysql.com/doc/refman/5.1/en/explain-output.html

Slough

"possible_keys denotes all the indices of your table (keys or index columns)" - not true. It denotes the indexes that could possibly used for the query in question. – Levelheaded