WHERE clause before INNER JOIN

mysql join optimization where
user1124535 picture user1124535 · Apr 13, 2012 · Viewed 34.6k times · Source

If I have

SELECT * FROM Table1 t1 
LEFT JOIN Table2 t2 ON t1.id = t2.id 
WHERE t1.user='bob';

Does the WHERE clause run after the two tables are JOINED?

How do I make it so it runs prior to the JOIN?

Answer

Mosty Mostacho picture Mosty Mostacho · Apr 13, 2012

The where clause will be executed before the join so that it doesn't join unnecessary records. So your code is fine the way it is.

Related questions

How do I tell the MySQL Optimizer to use the index on a derived table?

Suppose you have a query like this... SELECT T.TaskID, T.TaskName, TAU.AssignedUsers FROM `tasks` T LEFT OUTER JOIN ( SELECT TaskID, GROUP_CONCAT(U.FirstName, ' ', U.LastName SEPARATOR ', ') AS AssignedUsers FROM `tasks_assigned_users` TAU …

Read More
Mysql slow query: INNER JOIN + ORDER BY causes filesort

I'm trying to optimize this query: SELECT `posts`.* FROM `posts` INNER JOIN `posts_tags` ON `posts`.id = `posts_tags`.post_id WHERE (((`posts_tags`.tag_id = 1))) ORDER BY posts.created_at DESC; The size of tables is 38k rows, and 31…

Read More
What's the difference between INNER JOIN, LEFT JOIN, RIGHT JOIN and FULL JOIN?

What's the difference between INNER JOIN, LEFT JOIN, RIGHT JOIN and FULL JOIN in MySQL?

Read More