tags:

views:

125

answers:

7

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) 
+6  A: 

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.

Amber
I don't think you're right. Tables are not simply scanned through to evaluate conditions if not necessary. E.g., if an index exists for 'company'. The first subset selection is done using the index. If company wouldn't be evaluated first, the index couldn't be used anymore. However, a sophisticated DBMS should be able to decide which condition should be evaluated first to optimize the query.
chiccodoro
P.S. (cannot edit my comment anymore): Of course you're right in you second section, but the statement "the condition as a whole ... is evaluated for each row" is what I doubt.
chiccodoro
@chiccodoro: I think the optimizer takes care of that.
Gumbo
@chiccodoro: Sorry, bad wording on my part. Shouldn't be evaluated, should be more "examined". Evaluated implies that the value of each part is found, which wasn't what I was saying.
Amber
A: 

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

Aaron Harun
This would be true for full-table-scans but gladly the database has seldom to perform such a inefficent way.Mostly it will search in its (B-tree) index for entrys matching the criteria.
Tobias P.
+2  A: 

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).

oezi
+2  A: 

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.

chiccodoro
+1  A: 

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.

simendsjo
+1  A: 

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).

wimvds
+1  A: 

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.

They aren't the only choices. Some languages have short-circuit evaluation. Some promise to evaluate all of the expressions. Others have lazy evaluation, and only evaluate what they need in an undefined order. SQL (apparently) can choose its own order of evaluation for optimal short-circuit evaluation.
Oddthinking