Does order of where clauses matter in SQL?

sql performance where-clause
Ziyang Zhang picture Ziyang Zhang · Jul 11, 2012 · Viewed 74.6k times · Source

Let's say I have a table called PEOPLE having 3 columns ID, LastName, FirstName, none of these columns are indexed.
LastName is more unique, and FirstName is less unique.

If I do 2 searches:

select * from PEOPLE where FirstName="F" and LastName="L" 
select * from PEOPLE where LastName="L" and FirstName="F"

My belief is the second one is faster because the more unique criterion (LastName) comes first in the where clause, and records will get eliminated more efficiently. I don't think the optimizer is smart enough to optimize the first sql.

Is my understanding correct?

Answer

marc_s picture marc_s · Jul 11, 2012

No, that order doesn't matter (or at least: shouldn't matter).

Any decent query optimizer will look at all the parts of the WHERE clause and figure out the most efficient way to satisfy that query.

I know the SQL Server query optimizer will pick a suitable index - no matter which order you have your two conditions in. I assume other RDBMS will have similar strategies.

What does matter is whether or not you have a suitable index for this!

In the case of SQL Server, it will likely use an index if you have:

  • an index on (LastName, FirstName)
  • an index on (FirstName, LastName)
  • an index on just (LastName), or just (FirstName) (or both)

On the other hand - again for SQL Server - if you use SELECT * to grab all columns from a table, and the table is rather small, then there's a good chance the query optimizer will just do a table (or clustered index) scan instead of using an index (because the lookup into the full data page to get all other columns just gets too expensive very quickly).

Related questions

Is a JOIN faster than a WHERE?

Suppose I have two tables that are linked (one has a foreign key to the other): CREATE TABLE Document ( Id INT PRIMARY KEY, Name VARCHAR 255 ) CREATE TABLE DocumentStats ( Id INT PRIMARY KEY, DocumentId INT, -- this is a foreign key …

Read More
SQL - improve NOT EXISTS query performance

Is there a way I can improve this kind of SQL query performance: INSERT INTO ... WHERE NOT EXISTS(Validation...) The problem is when I have many data in my table (like million of rows), the execution of the WHERE NOT …

Read More
How does database indexing work?

Given that indexing is so important as your data set increases in size, can someone explain how indexing works at a database-agnostic level? For information on queries to index a field, check out How do I index a database column.

Read More