The WHERE clause order interferes with performance?

Question

The WHERE clause order interferes with performance?

Navigation

#1 by (2 votes)
#2 by (0 votes)
#3 by (0 votes)
#4 by (0 votes)

5

Recently I did tests on a database with a query using two clauses where AND. I noticed that there was a significant difference using clause A before B and vice versa.

Intuitively, it would be faster to use FIRST the more restrictive criterion, since it would have a smaller set to it then apply the second criterion. For example, based on the economically active Brazilian population, if I want to get all records of 'male' sex with 'income greater than 1 million dollars' it would be faster for me to first filter the records by income to only filter after sex .

I did a search and found this topic saying that there is no difference between using one after another, but it was not what I saw in practice. I have a table with almost 2 million records. If I use the most restrictive clause before the least restrictive, I spend less than 1 second on the query, in contrast to using the least restrictive 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 caching. I did the inversion that they suggest and got the same result, contrary to what was said there.

Can anyone explain me?

UPDATE (1)

I'm using MySQL 5 in my tests.

mysql sql query

asked by anonymous 22.12.2014 / 19:01

4 answers

Ionic3 Push notification with firebase XML digital signature with NodeJs

score 2 · Answer 1

Do both clauses make use of indexed columns?

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

It will also show you how SGDB works internally.

score 0 · Answer 2

Good afternoon! Well I do not know if it's right but let's go! When you use a WHERE clause and search for the record that will probably have more data, SQL will come more loaded because it searches for all the "people" that are male, as we know is much larger 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 think that's it, Embrace!

score 0 · Answer 3

Yes, there may be performance differences according to the WHERE order. MySQL tries to optimize the query, not always reaching the best plan possible.

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

score 0 · Answer 4

0

Yes, try putting always as the first clause reference dates for very large queries .. you will gain enough performace.

08.01.2015 / 18:27