Does the order of the WHERE clauses interfere with performance?

Asked

Viewed 1,841 times

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.

mysql sql query optimizing
  • 2

    And how are your tables and indexes? And put the darlings also. You probably have a specific problem.

  • What are the conditions? Mysql performs some optimizations, depending on the clauses http://dev.mysql.com/doc/refman/5.0/en/where-optimizations.html

  • 1

    Put the missing information before it fills with answers by kicking random things.

  • I am not using table indexes. I will try to set up a similar scheme to post here.

4 answers

2

Both clauses make use of indexed columns?

The SQL code is compiled and the SGDB mounts an execution plan. There are some tools that allow you to visualize the execution plan. In Mysql if I’m not mistaken, Mysql Workbench does this. Compare the execution plan of both queries, if different will justify the response time.

It will also show you how SGDB works internally.

0

Good afternoon! Well I don’t know if it’s right but come on ! When you use a WHERE clause and search for the record that will probably have more data, the SQL will come more loaded, because, it searches for all "people" who are male, as we know is far greater the amount of records than a person who earns more than 1 Million per month, so if you use the salary filter before it will be faster due to the difference in amount of data. I guess that’s it, hug !

0

Yes, try to always put as the first clause reference dates for very large queries.. you will earn enough performance.

0

Yes, there can be performance difference according to the order of the WHERE. Mysql tries to optimize the query, not always reaching the best possible plan.

One way to understand what happens is to use the EXPLAIN, that will detail you the optimization done. The lack of indexes probably causes no obvious optimization and Mysql follows the original query order. Note that a table with 2 million records not having index is probably a bad decision.

Browser other questions tagged mysql sql query optimizing

You are not signed in. Login or sign up in order to post.