MySQL sorting optimization - multiple table joins

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 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

Who is Participating?
Mark GilbertConnect With a Mentor Senior Performance EngineerCommented:
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.
Mark GilbertSenior Performance EngineerCommented:
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.
InterghostAuthor Commented:
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 = join table3 t3 on ... join tableN on = 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
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

Steve BinkCommented:
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.
InterghostAuthor Commented:
@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?
Steve BinkConnect With a Mentor Commented:
>>> 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.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.