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:


And Query 2:


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?

  • 3
    Now stop and restart the database, and run the queries in the opposite order. Welcome to the wonderful world of 'disk caching'. Jan 28, 2014 at 1:32
  • 8
    I don't know what book you have, but I'd stop reading it.
    – atxdba
    Jan 28, 2014 at 2:26
  • 2
    Wow, I agree fully with @atxdba - today's optimizers are a lot smarter than that. Well, maybe not MySQL's... Jan 28, 2014 at 3:22
  • The book is Sams Teach Yourself SQL in 24 hours, copyrighted in 2011. I'm not exactly surprised as there have been errors here and there in the book Jan 28, 2014 at 4:35
  • I was reading the same book from same publisher but a version for 21 days, and now I feel like I have been reading a waste of time. It was lent by a friend and I decided to give it a chance just to refresh some knowledge and look if there was something new and I read that part that got my attention. But I want to remark that the book it says "that's how Oracle works (IDK so much about Oracle) and you must check your RDBMS implementation". Jul 7, 2015 at 11:54

1 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/

  • Thanks. Are most DB engines' optimizers using cache of some sort? I'm using MS SQL Server and I would assume that Query Optimizer does. And in regards to the book, I'm going through Sams Teach Yourself SQL in 24 hours (copyrighted in 2011), which has had quite a few errors Jan 28, 2014 at 4:35
  • 1
    I know for certain that MS SQL Server does use a cache (several in fact). I would guess that most modern DB systems do. (Excluding maybe Access) Jan 28, 2014 at 5:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.