How to interpret the output of MySQL EXPLAIN?

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.

• 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