Mysql 7+ joins table, how to speed up work?
I have 7 tables. Table 1 is the relationship "parent table" and has_many
with tables 2-7.
I would like to make a query that grabs the rows of Table 1 with a list of IDs from Table 2-7. I tried the following query, but with a large database, my query takes 15 seconds. I am wondering how can I make this request faster?
Edit . A few notes: -> Removing Distinct only saves 4 seconds and I still get the request for 10-11 seconds
-> Removing 1 junction table (it doesn't matter which one) from the query reduces the time from 15 seconds to 2-3 seconds. Dropping 2 join tables (again doesn't matter what) reduces the query to 1/2 second.
SELECT
table1.table1_id as table1Id,
GROUP_CONCAT(DISTINCT table2.table2_id) AS table2Ids,
GROUP_CONCAT(DISTINCT table3.table3_id) AS table3Ids,
GROUP_CONCAT(DISTINCT table4.table4_id) AS table4Ids,
GROUP_CONCAT(DISTINCT table5.table5_id) AS table5Ids,
GROUP_CONCAT(DISTINCT table6.table6_id) AS table6Ids,
GROUP_CONCAT(DISTINCT table7.table7_id) AS table7Ids
FROM table1
LEFT JOIN table2 ON table1.table1_id = table2.table1_id
LEFT JOIN table3 ON table1.table1_id = table3.table1_id
LEFT JOIN table4 ON table1.table1_id = table4.table1_id
LEFT JOIN table5 ON table1.table1_id = table5.table1_id
LEFT JOIN table6 ON table1.table1_id = table6.table1_id
LEFT JOIN table7 ON table1.table1_id = table7.table1_id
WHERE table1.archived = false
GROUP BY table1.table1_id LIMIT 1000
My request for an explanation:
+----+-------------+------------------+------------+-------+----------------------------------------------------------------------------------------------------+---------------------------+---------+--------------------------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+------------------+------------+-------+----------------------------------------------------------------------------------------------------+---------------------------+---------+--------------------------+------+----------+-------------+
| 1 | SIMPLE | table1 | NULL | index | PRIMARY,unique_name_in_table8, table8_idx,table9_idx,table10_idx | PRIMARY | 4 | NULL | 1 | 10.00 | Using where |
| 1 | SIMPLE | table2 | NULL | ref | PRIMARY | PRIMARY | 4 | db.table1.table1_id | 20 | 100.00 | Using index |
| 1 | SIMPLE | table3 | NULL | ref | table3_to_table1_id_idx | table3_to_table1_id_idx | 4 | db.table1.table1_id | 824 | 100.00 | Using index |
| 1 | SIMPLE | table4 | NULL | ref | table4_word_unique,table4_to_table1_id_idx | table4_to_table1_id_idx | 4 | db.table1.table1_id | 4 | 100.00 | Using index |
| 1 | SIMPLE | table5 | NULL | ref | table5_to_table1_id_idx | table5_to_table1_id_idx | 4 | db.table1.table1_id | 26 | 100.00 | Using index |
| 1 | SIMPLE | table6 | NULL | ref | table6_to_table1_id_idx | table6_to_table1_id_idx | 4 | db.table1.table1_id | 3 | 100.00 | Using index |
| 1 | SIMPLE | table7 | NULL | ref | table7_to_table1_id_idx | table7_to_table1_id_idx | 4 | db.table1.table1_id | 483 | 100.00 | Using index |
+----+-------------+------------------+------------+-------+----------------------------------------------------------------------------------------------------+---------------------------+---------+--------------------------+------+----------+-------------+
source to share
I tried to rewrite the request. Try it and let me know. I have not tested it.
SELECT
t1.table1Id,
GROUP_CONCAT(DISTINCT table2.table2_id) AS table2Ids,
GROUP_CONCAT(DISTINCT table3.table3_id) AS table3Ids,
GROUP_CONCAT(DISTINCT table4.table4_id) AS table4Ids,
GROUP_CONCAT(DISTINCT table5.table5_id) AS table5Ids,
GROUP_CONCAT(DISTINCT table6.table6_id) AS table6Ids,
GROUP_CONCAT(DISTINCT table7.table7_id) AS table7Ids
FROM (
SELECT table1_id as table1Id
FROM table1
WHERE table1.archived = false
GROUP BY table1.table1_id LIMIT 1000
) AS t1
INNER JOIN table2 ON t1.table1Id = table2.table1_id
INNER JOIN table3 ON t1.table1Id = table3.table1_id
INNER JOIN table4 ON t1.table1Id = table4.table1_id
INNER JOIN table5 ON t1.table1Id = table5.table1_id
INNER JOIN table6 ON t1.table1Id = table6.table1_id
INNER JOIN table7 ON t1.table1Id = table7.table1_id
source to share
The explanation says it table2
has no index on table1Id
.
Try:
create index table2_to_table1_id_idx on table2(table1Id);
This can improve performance, but to maximize performance for this particular query, create covering indexes:
create index table2_table1_id_idx on table2(table1Id, table2Id);
create index table3_table1_id_idx on table3(table1Id, table3Id);
create index table4_table1_id_idx on table4(table1Id, table4Id);
create index table5_table1_id_idx on table5(table1Id, table5Id);
create index table6_table1_id_idx on table6(table1Id, table6Id);
create index table7_table1_id_idx on table7(table1Id, table7Id);
Having these indexes means you might not hit the underlying tables completely.
source to share