Welcome to WebmasterWorld Guest from

Forum Moderators: open

Message Too Old, No Replies

JOINS with different purposes - an effort to speed slow queries

3:01 pm on Mar 15, 2013 (gmt 0)

Junior Member

10+ Year Member

joined:Jan 18, 2008
posts: 100
votes: 0

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?
10:09 am on Mar 17, 2013 (gmt 0)

Preferred Member from AU 

10+ Year Member Top Contributors Of The Month

joined:May 27, 2005
votes: 7

I don't use joins at all unless I have to. If I need to get additional info from a related table, I use a function applied to the result. Doing it this way may have penalties but as they are only applied to the score of results returned from searching through thousands of results, I assume it to be much more efficient than a join sift of all records.
4:13 pm on May 3, 2013 (gmt 0)

Senior Member

WebmasterWorld Senior Member drdoc is a WebmasterWorld Top Contributor of All Time 10+ Year Member

joined:Mar 15, 2002
votes: 0

You can always use UNIONs and subqueries to determine the order in which queries are run and JOINs applied.

Subqueries run before outer ones (obviously), and queries are executed in order of appearance for UNIONs.

That said -- even complex JOINs on very large tables run fast ... if indexes are carefully applied, and sometimes forced. (May need to ANALYZE TABLE from time to time as well.)

What does EXPLAIN tell you?

Many times you will find that the query runs just fine with the JOINs, but that it's your WHERE or an ORDER BY that messes things up.

For complex queries, consider limiting your result set through JOINs in a subquery, the apply all the ORDER BY and GROUP BY in outer queries, along with appropriate WHERE statements.