0

have a table with approx 20,000 rows. Everything works fine but one of the queries in it has slowed down to about 5+ seconds. All of the tables have primary keys and their related indexes. There are also foreign keys and their related indexes. I have tried adding new indexes and trimming back the query bit by bit but it does not make a huge difference. I removed the order by too, that does indeed help but only so much. I was wondering if I could get a fresh pair of eyes to see if I'm missing something here.

SELECT DISTINCT 
t1.my_key,
t2.some_name,
t3.the_weather,
t4.another_value,
t5.hello_world,
t6.last_one
FROM
table_1 AS t1
INNER JOIN table_2 AS t2 
  ON t1.t2_id_fk = t2.t2_id_pk 
INNER JOIN table_3 AS t3 
  ON t1.t3_id_fk = t3.t3_id_pk
INNER JOIN table_4 as t4
  ON t1.t4_id_fk = t4.t4_id_pk 
LEFT JOIN table_5 AS t5 
  ON t1.t5_id_fk = t5.t5_id_pk
LEFT JOIN table_6 AS t6 
  ON t1.t6_id_fk = t6.t6_id_pk
LEFT JOIN (
  table_7 AS t7
  INNER JOIN table_8 AS t8
    ON (t7.t8_id_fk = t8.t8_id_pk)
           ) 
 ON (t1.t1_id_pk = t7.t1_id_fk)

I wonder what is causing the using_temporary especially when I have indexes defined for them all. For some reason when I run an explain it all looks good with only 1 row needed to be searched for each except for the first joined value from table 2. In this case it returns a using_temporary and needs to search almost 3000 rows each time.

Update I added a unique index to table_1 made up of the keys from the other joining tables in the same ordering. Explain tells me that this reduces the amount of rows to be searched from 3000 to 294 but the query execution time is still excessive. Its unusual now I must say.

jiraiya
  • 977
  • 4
  • 13
  • 34
  • I'm guessing that in the real query, t7 and t8 are actually used? – Joachim Isaksson Oct 05 '12 at 14:27
  • Exactly right, the real query is longer and has more fields but for the sake of brevity I trimmed them out but left the join in case it was a factor in the slow down. – jiraiya Oct 05 '12 at 14:29
  • if you don't use distinct, how many rows will it produce vs with distinct? – iouri Oct 05 '12 at 14:53
  • approx 1500 extra rows due to aggregation across the joins. I added an update above too take a look and see what you think. thanks. – jiraiya Oct 05 '12 at 14:57
  • Unfortunately, adding an index to `table_1` with all the fk values in the order you've written your query isn't quite what you're supposed to do - the optimizer is allowed to re-order your joins. You may want to play with adding different indices, and see which one it prefers (unfortunately, there are 7! possibilities, so don't bother with all of them). Perhaps put `t1.t2_id_fk` first? Although it maybe sounds like you may not have a matching index... – Clockwork-Muse Oct 05 '12 at 16:00
  • Ya I know what you mean. I did not have that index with all the rows in order. Initially I just had indexes on each of the key rows individually. But I said I'd give it a shot anyway. Thanks for the ideas and feedback though its appreciated. – jiraiya Oct 06 '12 at 17:19

0 Answers0