
Question:
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?
Solution:1
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).
Solution:2
SQL is declarative.
In your example, you have told the engine/optimizer what you want... it will now work out the best way to do that (within reason and "cost" which would be off topic).
Solution:3
Whereas in general, no, that assumes you're using a modern database. Maybe ten years ago, it certainly mattered then.
Solution:4
In short, no, they do not matter as the optimizer will determine the best means for fetching the data.
Note:If u also have question or solution just comment us below or mail us on toontricks1994@gmail.com
EmoticonEmoticon