How to interpret the output of MySQL EXPLAIN?

R_User picture R_User · Jan 3, 2013 · Viewed 10.2k times · Source

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)

Answer

Namphibian picture Namphibian · Jan 3, 2013

Answering your questions:

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.