Does the order of where clauses matter in SQL?

by

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

More Related Contents:

  1. Is a JOIN faster than a WHERE?
  2. How do I obtain a Query Execution Plan in SQL Server?
  3. Count(*) vs Count(1) – SQL Server
  4. SQL JOIN: is there a difference between USING, ON or WHERE?
  5. When should I use CROSS APPLY over INNER JOIN?
  6. Left Join With Where Clause
  7. SQL JOIN vs IN performance?
  8. Is a view faster than a simple query?
  9. Difference between Oracle’s plus (+) notation and ansi JOIN notation?
  10. What is the difference between HAVING and WHERE in SQL?
  11. MySQL INSERT INTO table VALUES.. vs INSERT INTO table SET
  12. Importance of varchar length in MySQL table
  13. SQL SELECT speed int vs varchar
  14. SQL ‘like’ vs ‘=’ performance
  15. How many rows in a database are TOO MANY?
  16. SQL server ignore case in a where expression
  17. What’s the best to check if item exist or not: Select Count(ID)OR Exist(…)?
  18. How does table alias names affect performance?
  19. Performance issue in using SELECT *? [duplicate]
  20. SQL count(*) performance
  21. Why is a UDF so much slower than a subquery?
  22. Improving performance of cluster index GUID primary key
  23. Two single-column indexes vs one two-column index in MySQL?
  24. How do you interpret a query’s explain plan?
  25. Which SQL statement is faster? (HAVING vs. WHERE…)
  26. “SELECT *” vs “SELECT ” for performance
  27. Challenge,how to implement an algorithm for six degree of separation?
  28. Entity Framework Skip/Take is very slow when number to skip is big
  29. Oracle , != , ^= operators
  30. When to use EXCEPT as opposed to NOT EXISTS in Transact SQL?

Leave a Comment