In MySQL, does the order of the columns in a WHERE clause affect query performance?
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.
From the documentation:
If the table has a multiple-column index, any leftmost prefix of the index can be used by the optimizer to find rows. For example, if you have a three-column index on (col1, col2, col3), you have indexed search capabilities on (col1), (col1, col2), and (col1, col2, col3).
MySQL cannot use an index if the columns do not form a leftmost prefix of the index.
So yes, it should be the same as the order of the columns in a compound index.
No, it doesn't matter.
The optimizer does a bunch of simple transformations straight after it parses the SQL - this is one of them.