Databases
StackOverflow2013
Postsdbo
POMysql query severe slow down with joins
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POMysql query severe slow down with joins
    primarykey
    data
    text
    <p>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.</p> <pre><code>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) </code></pre> <p>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.</p> <p>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.</p>
    singulars
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PTQuestion
    1. USjiraiya
    1. USjiraiya
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. COI'm guessing that in the real query, t7 and t8 are actually used?
      singulars
      1. POMysql query severe slow down with joins
      1. USJoachim Isaksson
    2. COExactly 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.
      singulars
      1. POMysql query severe slow down with joins
      1. USjiraiya
    3. COif you don't use distinct, how many rows will it produce vs with distinct?
      singulars
      1. POMysql query severe slow down with joins
      1. USiouri
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload