[Solved] Mysql query severe slow down with joins

EverSQL Database Performance Knowledge Base

Mysql query severe slow down with joins

Database type:

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.

How to optimize this SQL query?

The following recommendations will help you in your SQL tuning process.
You'll find 3 sections below:

  1. Description of the steps you can take to speed up the query.
  2. The optimal indexes for this query, which you can copy and create in your database.
  3. An automatically re-written query you can copy and execute in your database.
The optimization process and recommendations:
  1. Create Optimal Indexes (modified query below): The recommended indexes are an integral part of this optimization effort and should be created before testing the execution duration of the optimized query.
Optimal indexes for this query:
ALTER TABLE `table_2` ADD INDEX `table_2_idx_t2_pk` (`t2_id_pk`);
ALTER TABLE `table_3` ADD INDEX `table_3_idx_t3_pk` (`t3_id_pk`);
ALTER TABLE `table_4` ADD INDEX `table_4_idx_t4_pk` (`t4_id_pk`);
ALTER TABLE `table_5` ADD INDEX `table_5_idx_t5_pk` (`t5_id_pk`);
ALTER TABLE `table_6` ADD INDEX `table_6_idx_t6_pk` (`t6_id_pk`);
ALTER TABLE `table_7` ADD INDEX `table_7_idx_t1_fk` (`t1_id_fk`);
The optimized query:
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
                )

Related Articles



* original question posted on StackOverflow here.