mysql - Difference between where and and clause in join sql query

1. Performs the join, then the filter


2. Performs the filter before the join (better performance)

EDIT: Above was my original answer, below supports it.

4 Tables, 4 left joins, about 500,000 results and the second query runs in half the time. You should choose a larger working set when trying to test out query efficiency ... or factor in CPU load, RAM usage, connection time a few other things. If you are going to have less than 1,000 records or limited traffic optimization will be hard to see or justify. It's simple to test over your working set on a production environment and use what performs better (not just in theory).

http://www.beaudurrant.com/images/sof/22302649.jpg

You can see the differences in both efficiency and results over my datasets. This example is using a mySQL database.

1. 452,734 records in 420.016 seconds


2. 452,747 records in 214.334 seconds

Note: Queries were made to run slow on purpose.