问题描述
这是我的问题，我正在选择并进行多次连接以获取正确的项目...它吸引了相当多的行，超过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查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！