So joins have been my best friend for a long time now, but every so often they get complex and slow. After really looking into where the slowness happens and how mysql executes the query, I have realized that joins ultimately serve two purposes.

1) To help decide which rows to show in the result set
2) To help show additional information for each item in that result set.
and ultimately 3) a combination of 1 and 2.

From what I can tell, mysql executes the join statements for every single row in the database before it starts filtering out items from the WHERE and HAVING statements. This causes large tables with complex joins to run very slowly. But this is the way it has to work for scenarios 1 and 3. However if you only need the join to show additional information from the select and it doesn't in any way affect which rows are shown (scenario 2), then it seems there could be a huge performance boost if mysql would execute those joins after the WHERE, HAVING, ORDER BY, and LIMIT statements figured out the final result set.

I realize this is possible to achieve from doing a subquery in the SELECT, but since the premise of this conversation is complex joins this isn't always practical... especially if you need to pull multiple columns from the joined table.

So is there some way to tell mysql when a JOIN can be executed late in the statement?