CODEWITHSUNDEEP
mysqlsql
Greg
Greg

Reputation: 7922

Does the order of conditions in a WHERE clause affect MySQL performance?

Say that I have a long, expensive query, packed with conditions, searching a large number of rows. I also have one particular condition, like a company id, that will limit the number of rows that need to be searched considerably, narrowing it down to dozens from hundreds of thousands.

Does it make any difference to MySQL performance whether I do this:

 SELECT * FROM clients WHERE 
       (firstname LIKE :foo OR lastname LIKE :foo OR phone LIKE :foo) AND 
       (firstname LIKE :bar OR lastname LIKE :bar OR phone LIKE :bar) AND 
       company = :ugh

or this:

 SELECT * FROM clients WHERE 
       company = :ugh AND
       (firstname LIKE :foo OR lastname LIKE :foo OR phone LIKE :foo) AND 
       (firstname LIKE :bar OR lastname LIKE :bar OR phone LIKE :bar)

Upvotes: 56

Views: 23957

Answers (8)

Steve Chambers
Steve Chambers

Reputation: 39424

Here is a demo showing the order of WHERE clause conditions can make a difference due to short-circuiting. It runs the following queries:

-- query #1
SELECT myint FROM mytable WHERE myint >= 3 OR myslowfunction('query #1', myint) = 1;

-- query #2
SELECT myint FROM mytable WHERE myslowfunction('query #2', myint) = 1 OR myint >= 3;

The only difference between these is the order of operands in the OR condition.

myslowfunction deliberately sleeps for a second and has the side effect of adding an entry to a log table each time it is run. Here are the results of what is logged when running the two queries:

myslowfunction called for query #1 with value 1
myslowfunction called for query #1 with value 2
myslowfunction called for query #2 with value 1
myslowfunction called for query #2 with value 2
myslowfunction called for query #2 with value 3
myslowfunction called for query #2 with value 4

The above shows that a slow function is executed more times when it appears on the left side of an OR condition when the other operand isn't always true.

So IMO the answer to the question:

Does the order of conditions in a WHERE clause affect MySQL performance?

is "Sometimes it can do."

Upvotes: 36

Amber
Amber

Reputation: 526593

No, the order should not make a large difference. When finding which rows match the condition, the condition as a whole (all of the sub-conditions combined via boolean logic) is examined for each row.

Some intelligent DB engines will attempt to guess which parts of the condition can be evaluated faster (for instance, things that don't use built-in functions) and evaluate those first, and more complex (estimatedly) elements get evaluated later. This is something determined by the DB engine though, not the SQL.

Upvotes: 24

Neel Basu
Neel Basu

Reputation: 12904

Mathematically Yes It has an effect. Not only in SQL Query. rather in all programming languages whenever there is an expression with and / or . There works a theory of Complete evaluation or partial evaluation. If its an and query and first expression of and evaluates to false it will not check further. as anding false with anything yields false . Similerly in an or expression if first one is true it will not check further.

Upvotes: 5

wimvds
wimvds

Reputation: 12850

The order of columns in your where clause shouldn't really matter, since MySQL will optimize the query before executing it. But I suggest you read the chapter on Optimization in the MySQL reference manual, to get a basic idea on how to analyze queries and tables, and optimize them if necessary. Personally though, I would always try to put indexed fields before non-indexed fields, and order them according to the number of rows that they should return (most restrictive conditions first, least restrictive last).

Upvotes: 8

simendsjo
simendsjo

Reputation: 4749

I don't think the order of the where clause has any impact. I think the MySQL query optimizer will reorganize where clauses as it sees fit so it filters away the largest subset first.

It's another deal when talking about joins. The optimizer tries to reorder here too, but doesn't always finds the best way and sometimes doesn't use indexes. SELECT STRAIGHT JOIN and FORCE INDEX let's you be in charge of the query.

Upvotes: 3

chiccodoro
chiccodoro

Reputation: 14716

A sophisticated DBMS should be able to decide on its own which where condition to evaluate first. Some Databases provide tools to display the "strategy" how a query is executed. In MySQL, e.g. you can enter EXPLAIN in front of a query. The DBMS then prints the actions it performed for executing the query, as e.g. index or full-table scan. So you could see at a glance whether or not it uses the index for 'company' in both cases.

Upvotes: 4

oezi
oezi

Reputation: 51797

this shouldn't have any effect, but if you aren't sure, why don't you simply try it out? the order of where-clauses on an select from a single table makes no difference, but if you join multiple tables, the order of the joins could affect the performace (sometimes).

Upvotes: 3

Aaron Butacov
Aaron Butacov

Reputation: 34347

No it doesn't, the tables required are selected and then evaluated row by row. Order can be arbitrary.

Upvotes: 1

Related Questions