0

I have a query that is returning 17,000 rows on a table called 'history'. It has about 15 left joins to other tables mainly based on history.id = othertable.primary_id.

When I remove order by history.primary_id DESC the query is very fast. When order by history.primary_id DESC is there MySQL takes about 26 seconds to write to a temp table..so very slow.

However when "order by" is present, but the joins are omitted, it is quick.

I do not understand why the data being written to the temp table is dependent on the joins? Wouldn't MySQL already know what the 17,000 rows consist of by that point as it is after the query executes?

According to "EXPLAIN" my indexes are set up pretty well, but for the life of me I cannot sort this query. I want to use a limit of 1000 but it is useless when I cannot sort. I cant seem to get it to not use a temp table.

Here is a screenshot of the query.

hjpotter92
  • 78,589
  • 36
  • 144
  • 183
Tony Gutierrez
  • 753
  • 1
  • 6
  • 16
  • Are you ordering by columns in the joined tables - if you are sorting by the primary key (which is a foreign key in other tables then you implicitly are). This could cause the query to have to evaluate the full join of all tables before it can work out which rows you actually want. One way to test if this is the case is to omit the order by from the original query, then do: `SELECT * FROM (original query) ORDER BY columns` - this should then only act on the result of the inner query (post join operations). – dash Sep 24 '12 at 23:06
  • I am ordering by history.history_id which is the primary key of the main table. I will try your suggestion. – Tony Gutierrez Sep 24 '12 at 23:15
  • Wrapping the query in a select results in it being copied to a tmp_table, even when the inner query alone is quick. – Tony Gutierrez Sep 24 '12 at 23:22
  • Any chance you could post the output of the EXPLAIN, including the Extra column on your original query with order by please? Creating an in-memory temp table isn't a bad thing, it's one of the ways in which MySql breaks down it's results handling. Here's an example of the first comment btw: http://stackoverflow.com/questions/884661/slow-query-when-using-order-by – dash Sep 24 '12 at 23:24
  • Here is a [screen shot of the explain.](http://cl.ly/image/372x1E1E0t02) – Tony Gutierrez Sep 24 '12 at 23:30
  • Ah! Is there anyway you can break the query down into two parts? One that gets all the core data so you can order it, and then one that looks up the additional data from that? The temporary table and filesort mean it is almost certainly having to pull all records from all of the tables into one temporary table to perform the sort. – dash Sep 24 '12 at 23:36
  • Yeah, thought I might have to do that. I just thought I would ask as it seems like this should totally work...the sort I want is essentially a reverse, and doing it after the fact in php is very quick..but I need the limit as well and it can take seconds just to transfer the row data if I do not limit the original query. As far as your suggestion, should I just use WHERE history.history_id IN (list of ids from prev query) ?? – Tony Gutierrez Sep 24 '12 at 23:53
  • Add an index on `history (fk_project, dt_created)` – ypercubeᵀᴹ Sep 25 '12 at 00:05

0 Answers0