The most restrictive condition be listed last in the where clause

condition

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?

Best Answer

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/

Related Solutions

SQL Server – Logical Operators OR AND in Condition and Order of Conditions in WHERE

There's no guarantee in SQL Server if or in which order the statements will be processed in a WHERE clause. The single expression that allows statement short-circuiting is CASE-WHEN. The following is from an answer I posted on Stackoverflow:

How SQL Server short-circuits WHERE condition evaluation

It does when it feels like it, but not in the way you immediately think of.

As a developer you must be aware that SQL Server does not do short-circuiting like it is done in other programming languages and there's nothing you can do to force it to.

For further details check the first link in the above blog entry, which is leading to another blog:

Does SQL Server Short-Circuit?

The final verdict? Well, I don't really have one yet, but it is probably safe to say that the only time you can ensure a specific short-circuit is when you express multiple WHEN conditions in a CASE expression. With standard boolean expressions, the optimizer will move things around as it sees fit based on the tables, indexes and data you are querying.

PostgreSQL WHERE Clause – Prioritizing Conditions with OR

You have two conditions to check during the authentication:

There is a user with matching username; do not need to match by email.
There are no users with matching usernames; need to match by email.

So the query is:

WITH matching_username AS (SELECT * FROM member WHERE username = 'john@domain.com')
SELECT * FROM matching_username WHERE (SELECT COUNT(*) FROM matching_username) = 1
                                   AND pw_hash = 'x1j34'
UNION 
SELECT * FROM member            WHERE (SELECT COUNT(*) FROM matching_username) = 0
                                   AND email = 'john@domain.com' 
                                   AND pw_hash='x1j34';

On the other hand, there is a problem with your design of the table.

What if two users have have the same email, but neither of them have email as their username? If one of them tries to authenticate with an email, you will not be able to tell, which user record to authenticate against.

Best Answer

Related Solutions

SQL Server – Logical Operators OR AND in Condition and Order of Conditions in WHERE

PostgreSQL WHERE Clause – Prioritizing Conditions with OR

Related Question