2

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?

  • 3
    Now stop and restart the database, and run the queries in the opposite order. Welcome to the wonderful world of 'disk caching'. – Pieter Geerkens Jan 28 '14 at 1:32
  • 8
    I don't know what book you have, but I'd stop reading it. – atxdba Jan 28 '14 at 2:26
  • 2
    Wow, I agree fully with @atxdba - today's optimizers are a lot smarter than that. Well, maybe not MySQL's... – Aaron Bertrand Jan 28 '14 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 – simplycoding Jan 28 '14 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". – Maximus Decimus Jul 7 '15 at 11:54
5

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 – simplycoding Jan 28 '14 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) – Kenneth Fisher Jan 28 '14 at 5:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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