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)
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.
