MySQL sorting optimization - multiple table joins

Posted on 2007-12-03
Medium Priority
Last Modified: 2013-12-12
I have n tables that are joined in a select query, all the where conditions are on n-1 tables, and all the columns that can be sorted are on the remaining 1 table.
When doing a join mysql always selects one of the tables with where conditions as the "first" table meaning no index is used for sorting and I always get a tmp table/filesort which, depending on the num of records, can get slow.

From MySQL manual: "You are joining many tables, and the columns in the ORDER BY are not all from the first non-constant table that is used to retrieve rows. (This is the first table in the EXPLAIN output that does not have a const join type.)".

This makes sense to me because by selecting the table with the least amount of rows that match the where criteria as first drasticly reduces the resultset to work with....so forcing the join in reverse order isn't good as an index is used for sorting but the time required to execute is a lot longer because the resultset isn't filtered by the joins first and all the rows are scaned.

My question ...Is there any way to optimize this better... and how?

Open in new window

Question by:Interghost
  • 2
  • 2
  • 2
LVL 18

Expert Comment

by:Mark Gilbert
ID: 20398006
Use left or right joins and use specific names for the field names, such as select table1.ID, table1.Name, table2.Description instead of select *.  Additionally, by using a left or right join, you are only returning rows from the first table which meet a criteria instead of everything.  So if table one had a few records with blank ID's (which I would certainly hope wouldn't exist...but this is an example), then your results would only contain records with values in table1.ID.  Hope this helps.

Author Comment

ID: 20398169
using left/right join means the result set won't get filtered (thats the main idea of using "normal" joins in this case, I need rows present in all tables and normal join with conditions on each table filters the resultset perfectly)

...I think you missed the point of my question, I need help in optimizing sorting
example query:
select t1.* from table1 t1 join table2 t2 on t1.id = t2.id join table3 t3 on t1.id=t3.id ... join tableN on t1.id = tN.id where t2.something = something and t3.something = something ... and tN.something = something  [>>>] order by t1.title [<<<]

...notice no where condition on t1 table, but order by on t1 table
the above query will use indexes for every join / where condition but will do a filesort of the resultset at the end which gets slow as the num of rows increases and thats my problem
LVL 18

Accepted Solution

Mark Gilbert earned 1350 total points
ID: 20398188
Ahh, i see what you mean and my apologies.  If your join statements are pretty complex and aren't sorting as quickly as you would expect them to, have you considered the option of creating a temporary table, storing the result in that and then doing your sorting therein?  You may find it improves the result times.
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

LVL 51

Expert Comment

by:Steve Bink
ID: 20398334
Have you followed the advice found here?


I think the item tripping you up is this one:

# The key used to fetch the rows is not the same as the one used in the ORDER BY:

SELECT * FROM t1 WHERE key2=constant ORDER BY key1;

Using inqwa's temporary table strategy may be of some help to you in this regard.

Author Comment

ID: 20406962
@ingwa ... hm, yes temp. tables also crossed my mind earlier today, did a quick test with a  create temporary ... select   -  but the gain wasn't as big as I hoped and I didn't have enought time for an entire test... and it was also only create select without another sorted select from the temp table... I'll try making a whole testcase tomorrow and see how it goes.

One more thing I forgot to mention... the query in my case is progressive, ie it starts with N = 2 lets say and then N increases... the bigger the N (number of joined tables / conditions) the smaller the resultset and thus faster query execution cause of fewer items to sort, kinda ironic...more complex query, faster execution :))
I think temp.tables would probably do some help in the first (maybe even second) iteration of N, but after that it would probably become slower and I'm searching for a solution that would work with every iteration...if there is one of course...if not I guess I'll just have to live with the way things are + cache

@routinet: that manual page lists basic single-table examples that don't apply here, correct me if I'm wrong (and yes I have read through it a few times before :))

PS: can someone remind me how do I assign partial points?
LVL 51

Assisted Solution

by:Steve Bink
Steve Bink earned 150 total points
ID: 20407378
>>> that manual page lists basic single-table examples that don't apply here, correct me if I'm wrong

The examples are basic, true, but the deeper concept holds true for more complex queries as well.  Your query is using keys a, b, and c to select rows, but you are ordering by key d.  In that case, according to the example I posted before, MySQL will use a filesort on the results.

This means the basic problem of the filesort is that you are using selection keys which are not used for ordering.  To work around that limitation, you can insert your query results into a temporary table, then re-select them with the appropriate ORDER BY.  It will certainly be a little slower than if you were able to execute a direct sort on the results, but there will not be much of a hit.  

You can split the points by clicking the "Accept Multiple Solutions" link below any comment.  On the following screens, assign the points as you want, and the site will do the rest.

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In today's business world, data is more important than ever for informing marketing campaigns. Accessing and using data, however, may not come naturally to some creative marketing professionals. Here are four tips for adapting to wield data for insi…
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses
Course of the Month16 days, 15 hours left to enroll

864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question