Why should the most restrictive condition be listed last in the where clause?

Question

The book I'm going through has given a rather poor explanation of why exactlt to place the most restrictive condition last in the WHERE clause.

Say that we have Query 1:

SELECT COUNT(*)
FROM TEST
WHERE LAST_NAME = 'SMITH'
AND CITY = 'INDIANAPOLIS';

And Query 2:

SELECT COUNT(*)
FROM TEST
WHERE CITY = 'INDIANAPOLIS'
AND LAST_NAME = 'SMITH';

and the first query takes 20 seconds, while the 2nd query only takes 10. The book goes on to say that "Because the 2nd query returned faster results and the most restrictive condition was listed last in the WHERE clause, it is safe to assume that the optimizer reads the WHERE clause from the bottom up.

I'm confused as to how I'm supposed to make that inference. Is it that filtering after 1 condition is much slower than the first filter?

Answer 1

That was the case many years ago (back when I started working with databases). These days however the optimizers are smart enough that it doesn't matter what order you put the restrictions in the WHERE clause.

What @PieterGeerkens mentioned was something called caching where the optimizer is smart enough to take the information it needed for the query you ran and put it into memory. This of course provides much quicker access than disk IO. Then when you run the second query all (or most depending on the size of your data and RAM) of your data will already be in memory. This means that your second query will go faster. And again as Pieter said if you clear your cache then run the queries in reverse order the second query will again go faster.

I'm hoping your book is just really old but either way I would find a different one. There are a number of good free books out there along with other free training materials. Here is a page with some good links. http://sqlstudies.com/study-and-reference-tools/