I understand the joins when I read them but soon after I've read I forget about them. It can be because I don't use them much daily. Is there a practical way to remember them easily?
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
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 ^^
j
stuff ;)
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:
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.