Comment by j on "How to understand & remember database joins?"

this picture:

And in general the important thing of an SQL query is

-> the "ON" and the "WHERE" clause will be resolved left to right -> so first we use indexed fields and the smalles possible set and than we use the others. This makes your query much faster

you can always use "EXPLAIN" in front of your query.

Edit:

-> the "ON" and the "WHERE" clause will be resolved left to right -> so first we use indexed fields and the smalles possible set and than we use the others. This makes your query much faster

as pointed out below by Bill O'Neil this is not true, the query optimizer does usually fix this problem. It's an old habit that's got stuck

here is detailed explaination how a query optimizer works in general

stackoverflow.com/questions/4035760/does-the-orde…

However on certain database engines sadly it still can make a difference.

the "ON" and the "WHERE" clause will be resolved left to right

That's not true. The query optimizer will pick whichever indexes it thinks is best for the query no matter what order they are in. When you get into sub queries you want to make sure each individual subquery is returning as little data as possible.

Bill O'Neil ah thank you, just checked again, the query optimizer should do the work in modern SQL-Database depending on which version and so on.

But the real urban myth I wrote was "the smallest set first" because that's actually what's not happening. use-the-index-luke.com/sql/myth-directory/most-se… thx :)

That is specifically for compound / composite indexes and the order matters in the index creation time. As long as your query has the correct indexes included the order still doesn't matter. If you have an index (a, b, c) and the where clause of your query has where b = ? and a = ? it will still use the first two segments of the index.

Bill O'Neil did not say you're wrong just said what my urban myth was :) but thx for explaining it I put a detailed description up in the post how a query optimizer works based on an stack overflow answer. :) thx for pointing out the mistake

Wasn't trying to say you were wrong. It's a great technique to mention. It's one of the most overlooked SQL optimizations I run across. Everyone tends to just index individual fields but there is a lot to be gained from composite indexes.

Bill O'Neil that's true, I myself tend to mainly use it as in unique constraints. I think it's time for a refresher in SQL anyways ;D I will read some books about it ^^

Search Hashnode