How to interpret MySQL EXPLAIN output?

Question

I want to select the contents of a text column 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 indexes:

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

EXPLAIN result:

 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 

• Are possible_keys MySQL indexes that you might want to use, and keys MySQL indexes actually use?
• Why is index_status_mit_spam not used? The columns in the query are in the same order as in the index, ...
• Why is index_datum not used for ORDER BY ?
• How can I optimize my index tables or query? (The query above takes up to 3 seconds, having about a million entries in the table).

Answer 1

Answering your questions:

• Is MySQL indexes possible_keys, you might want to use and MySQL index keys actually using? Yes, that's right.

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

• Why is index_datum not used for ORDER BY ? MySQL will use only one index, not two at the time of the query, since using the second index will slow down the query even more. Reading the index does NOT read the table. This is due to the operational efficiency of the request. Here are answers that some concepts may explain: https://dba.stackexchange.com/questions/18528/performance-difference-between-clustered-and-non-clustered-index/18531#18531 or Adding a limit sentence to a MySQL query is slowing down is it sharp or MySQL indexes and when to group them . These answers contain many details to help you understand MySQL indexing.

• How can I optimize my table indexes or query? (The above query takes up to 3 seconds, having about a million entries in the table). Well, there is file sorting here, which probably slows you down. There may be too many indexes in the table, and MySQL is choosing the wrong one.

You should understand that indexes speed up reading and slow down writing to tables. So just adding indexes is not always a good idea. The above answers and pointers should help you get a solid understanding.

Answer 2

• possible_keys denotes all indexes on your table (keys or index columns)
• The MySQL optimizer decides the best way to execute an EXECUTE query; it can use either an index (optional primary key) or none
• To force MySQL to use or ignore the index specified 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 index tooltip area by adding a FOR clause to the tooltip. This provides finer-grained control over the choice of an optimizer for the execution plan for the various stages of query processing. To affect only the indexes used when MySQL decides how to find rows in a table and how to handle joins, use FOR JOIN. To influence the use of an index to sort or group rows, use FOR ORDER BY or FOR GROUP BY. (However, if there is a coverage index for the table and it is used to access the table, the optimizer will ignore the IGNORE INDEX FOR {ORDER BY | GROUP BY} hints that prohibit this index.)

• Try using a different index - check this link for sure - Using MySQL` FORCE INDEX`?

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