如何通过多个联接加快MySQL查询 [英] How can I speed up MySQL query with multiple joins

问题描述

这是我的问题，我正在选择并进行多次连接以获取正确的项目...它吸引了相当多的行，超过100,000.当日期范围设置为1年时，此查询将花费5分钟以上的时间.

Here is my issue, I am selecting and doing multiple joins to get the correct items...it pulls in a fair amount of rows, above 100,000. This query takes more than 5mins when the date range is set to 1 year.

我不知道是否可能，但恐怕用户会将日期范围扩展到10年之久并使其崩溃.

I don't know if it's possible but I am afraid that the user might extend the date range to like ten years and crash it.

有人知道我该如何加快速度吗?这是查询.

Anyone know how I can speed this up? Here is the query.

SELECT DISTINCT t1.first_name, t1.last_name, t1.email 
FROM table1 AS t1 
INNER JOIN table2 AS t2 ON t1.CU_id = t2.O_cid 
INNER JOIN table3 AS t3 ON t2.O_ref = t3.I_oref 
INNER JOIN table4 AS t4 ON t3.I_pid = t4.P_id 
INNER JOIN table5 AS t5 ON t4.P_cat = t5.C_id 
WHERE t1.subscribe =1 
AND t1.Cdate >= $startDate
AND t1.Cdate <= $endDate
AND t5.store =2

我不是MySQL方面的佼佼者，因此不胜感激！

I am not the greatest with mysql so any help would be appreciated!

提前谢谢！

更新

这是您要求的说明

id  select_type     table   type    possible_keys   key     key_len     ref     rows    Extra
1   SIMPLE  t5  ref     PRIMARY,C_store_type,C_id,C_store_type_2    C_store_type_2  1   const   101     Using temporary
1   SIMPLE  t4  ref     PRIMARY,P_cat   P_cat   5   alphacom.t5.C_id    326     Using where
1   SIMPLE  t3  ref     I_pid,I_oref    I_pid   4   alphacom.t4.P_id    31   
1   SIMPLE  t2  eq_ref  O_ref,O_cid     O_ref   28  alphacom.t3.I_oref  1    
1   SIMPLE  t1  eq_ref  PRIMARY     PRIMARY     4   alphacom.t2.O_cid   1   Using where

我还添加了一个索引到table5行和table4行，因为它们并没有真正改变，但是其他表每个月大约有500-1000个条目...我听说您应该向具有该行的表添加索引许多新条目....是真的吗?

Also I added an index to table5 rows and table4 rows because they don't really change, however the other tables get around 500-1000 entries a month... I heard you should add an index to a table that has that many new entries....is this true?

推荐答案

我将尝试以下操作:

首先，确保以下表和列上都有索引(括号中的每组列都应是一个单独的索引):

First, ensure there are indexes on the following tables and columns (each set of columns in parentheses should be a separate index):

table1 : (subscribe, CDate)
         (CU_id)
table2 : (O_cid)
         (O_ref)
table3 : (I_oref)
         (I_pid)
table4 : (P_id)
         (P_cat)
table5 : (C_id, store)

第二，如果添加上述索引并没有达到您想要的效果，请尝试将查询重写为

Second, if adding the above indexes didn't improve things as much as you'd like, try rewriting the query as

SELECT DISTINCT t1.first_name, t1.last_name, t1.email FROM
  (SELECT CU_id, t1.first_name, t1.last_name, t1.email
     FROM table1
     WHERE subscribe = 1 AND
           CDate >= $startDate AND
           CDate <= $endDate) AS t1
  INNER JOIN table2 AS t2
    ON t1.CU_id = t2.O_cid   
  INNER JOIN table3 AS t3
    ON t2.O_ref = t3.I_oref   
  INNER JOIN table4 AS t4
    ON t3.I_pid = t4.P_id   
  INNER JOIN (SELECT C_id FROM table5 WHERE store = 2) AS t5
    ON t4.P_cat = t5.C_id

我希望在这里，第一个子选择将大大减少要考虑加入的行数，希望使以后的加入工作更少.同上表5中第二个子选择的原因.

I'm hoping here that the first sub-select would cut down significantly on the number of rows to be considered for joining, hopefully making the subsequent joins do less work. Ditto the reasoning behind the second sub-select on table5.

无论如何，请弄乱它.我的意思是，最终它只是一个SELECT-您不能用它真正伤害任何东西.检查每个不同排列生成的计划，并尝试找出每个计划的优缺点.

In any case, mess with it. I mean, ultimately it's just a SELECT - you can't really hurt anything with it. Examine the plans that are generated by each different permutation and try to figure out what's good or bad about each.

分享并享受.

这篇关于如何通过多个联接加快MySQL查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！