sql查询联接多个表-太慢了(8个表) [英] sql query joins multiple tables - too slow (8 tables)

问题描述

我试图将8个表合并为一个表，以创建供其他应用程序使用的索引，我的查询就像:(我的mysql技能非常业余)

i'm trying to join 8 tables into one in order to create index used by other application, my query is like : (my mysql skill's very amateur)

SELECT t1_id, t2_name, t3_name, t4_name, t5_name, 
       t6_name, t7_name, t8_name, t9_name 
FROM t1 
  LEFT JOIN t2 ON (t1_id = t2_id) 
  LEFT JOIN t3 ON (t3_id = t1_id) 
  LEFT JOIN t4 ON (t4_id = t1_id)
  LEFT JOIN t5 ON (t5_id = t1_id)
  LEFT JOIN t6 ON (t6_id = t1_id) 
  LEFT JOIN t7 ON (t7_id = t1_id)
  LEFT JOIN t8 ON (t8_id = t1_id)
  LEFT JOIN t9 ON (t9_id = t1_id)

执行查询时，我什至看不到查询结果，有什么方法可以加快查询速度? :)可以提供各种帮助，但是最好仅执行一个查询(应用程序规则之外)

i can't even see the query results when i executing it, any ways to speed it up? :) any kinds of help is appreciated, but it's better be only one query (outside application rules)

预先感谢

推荐答案

我遇到了类似的问题，即几个查找表连接到一个具有所有id字段都已编入索引的大型表中.为了监视联接对查询时间执行的影响，我多次运行查询(限制为前100行)，每次都将Join添加到另一个表中.连接12个表后，查询执行时间没有明显变化.当我加入第13张桌子时，执行时间跃升至1秒.第14桌4秒，第15桌20秒，第16桌90秒.

I had a similar problem with several lookup tables joining to a large table with all id fields indexed. To monitor the effect of the joins on query time execution, I ran my query several times (limiting to first 100 rows), adding a Join to an additional table each time. After joining 12 tables, there was no significant change in query execution time. By the time I had joined the 13th table the execution time jumped to a 1 second; 14th table 4 seconds, 15th table 20 s, 16th 90 seconds.

Keijro的建议是使用关联的子查询，而不要使用例如联接.

Keijro's suggestion to use a correlated subqueries instead of joins e.g.

SELECT t1_id, 
        (select t2_name from t2 where t1_id = t2_id), 
        (select t3_name from t3 where t1_id = t3_id), 
        (select t4_name from t4 where t1_id = t4_id), 
        (select t5_name from t5 where t1_id = t5_id), 
        (select t6_name from t6 where t1_id = t6_id), 
        (select t7_name from t7 where t1_id = t7_id), 
        (select t8_name from t8 where t1_id = t8_id), 
        (select t9_name from t9 where t1_id = t9_id)  FROM t1

大大提高了查询性能.实际上，子查询似乎并没有延长执行查询的时间(查询几乎是瞬时的).

improved query performance dramatically. In fact the subqueries did not seem to lengthen the time to execute the query (the query was almost instanteous).

我感到有些惊讶，因为我认为相关子查询的性能要比联接差.

I am a little suprised as I thought correlated subqueries perform worse than joins.

这篇关于sql查询联接多个表-太慢了(8个表)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！