5
I recently ran some tests on a database with a query using two Where AND clauses. I noticed that there was a significant difference using clause A before clause B and vice versa.
Intuitively, it would be faster to use the most restrictive criterion FIRST, as it would have a smaller set to then apply the second criterion. For example, on the basis of the economically active Brazilian population, if I want to obtain all 'male' records with 'income over 1 million dollars' it would be faster if I first filtered the records by income and then filtered by gender.
I did some research and found this topical saying there’s no difference between using one after the other, but that’s not what I saw in practice. I have a chart with almost 2 million records. If I use the more restrictive clause before the less restrictive one, I spend less than 1 second on the query, in contrast to using the less restrictive one before, when the search takes almost 20 seconds. And I’m using the SQL_NO_CACHE clause in the query, that is, I’m not using cache. I did the inversion that they suggest and obtained the same result, contrary to what was said there.
Can someone explain to me?
UPDATE (1)
I am using Mysql 5 in my tests.
And how are your tables and indexes? And put the darlings also. You probably have a specific problem.
– Maniero
What are the conditions? Mysql performs some optimizations, depending on the clauses http://dev.mysql.com/doc/refman/5.0/en/where-optimizations.html
– Filipe Roxo
Put the missing information before it fills with answers by kicking random things.
– Maniero
I am not using table indexes. I will try to set up a similar scheme to post here.
– dellasavia