In MySQL, does the order of the columns in a WHERE clause affect query performance?

I am having performance issues on certain database queries that have large possible result sets.

The query in question, I have three ANDs in the WHERE clause

Does the order of the clauses matter?

As in, if I put the ASI_EVENT_TIME clause first (since that would remove the most of the results out of any of the clauses.

Will that improve the run time on the query?

QUERY:

SELECT DISTINCT  activity_seismo_info.* 
FROM `activity_seismo_info` 
WHERE 
    activity_seismo_info.ASI_ACTIVITY_ID IS NOT NULL  AND 
    activity_seismo_info.ASI_SEISMO_ID IN (43,44,...,259) AND 
    (
        activity_seismo_info.ASI_EVENT_TIME>='2011-03-10 00:00:00' AND 
        activity_seismo_info.ASI_EVENT_TIME<='2011-03-17 23:59:59'
    ) 

ORDER BY activity_seismo_info.ASI_EVENT_TIME DESC

EXPLAIN of query:

+----+-------------+---------+-------+---------------------------+--------------+---------+------+-------+-----------------------------+ 
| id | select_type | table   | type  | possible_keys             | key          | key_len | ref  | rows  | Extra                       |
+----+-------------+---------+-------+---------------------------+--------------+---------+------+-------+-----------------------------+ 
|  1 | SIMPLE      | act...o | range | act...o_FI_1,act...o_FI_2 | act...o_FI_1 | 5       | NULL | 65412 | Using where; Using filesort |
+----+-------------+---------+-------+---------------------------+--------------+---------+------+-------+-----------------------------+

Using:

PHP 5.2

MySQL 5.0.51a-3ubuntu5.4

Propel 1.3

Symfony 1.2.5

I do not think so. The query optimizer should be clever enough.

You can try rearranging the WHERE clauses and see that EXPLAINS tells you the same in each case.

About what can be done to optimize this query: Is there an index on ASI_EVENT_TIME ? (this is the most crucial I think for this query as you also sort the results using it).

Are there indexes on the other two fields (ASI_SEISMO_ID and ASI_ACTIVITY_ID)?

It would be helpful if you posted the table structure.

StackExchange Database Administrators Q#1792, answer score: 31