Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

SQL - Does the order of WHERE conditions matter?

Tags:

sql

Assume that category_id is an index key (not primary key) of table books. Is there any difference between the following two SQL statements?

SELECT * FROM books WHERE author='Bill' AND category_id=1  SELECT * FROM books WHERE category_id=1 AND author='Bill' 

I guess filtering records first by category_id and then by author is faster than filtering them in reverse order. Are SQL engines smart enough to do it this way?

like image 628
powerboy Avatar asked Jun 30 '10 18:06

powerboy


People also ask

Does the order of WHERE conditions matter SQL?

In SQL Server order does not matter in the WHERE condition. SQL Server does not short circuit conditions as well it does not help in performance.

Does SQL execute WHERE in order?

Six Operations to Order: SELECT, FROM, WHERE, GROUP BY, HAVING, and ORDER BY. By using examples, we will explain the execution order of the six most common operations or pieces in an SQL query. Because the database executes query components in a specific order, it's helpful for the developer to know this order.

Does SELECT order matter in SQL?

To put it simply: yes, it does. The order of the columns matters when it comes to improving performance of queries in your SQL Server.

How does WHERE condition works in SQL?

The SQL WHERE clause is used to specify a condition while fetching the data from a single table or by joining with multiple tables. If the given condition is satisfied, then only it returns a specific value from the table. You should use the WHERE clause to filter the records and fetching only the necessary records.


1 Answers

No, the order of the WHERE clauses does not matter.

The optimizer reviews the query & determines the best means of getting the data based on indexes and such. Even if there were a covering index on the category_id and author columns - either would satisfy the criteria to use it (assuming there isn't something better).

like image 187
OMG Ponies Avatar answered Oct 11 '22 11:10

OMG Ponies