MySQL Query占用时间超过6秒 [英] MySQL Query taking over 6 seconds
问题描述
一段时间后,我得到了一些特定查询的帮助。这是链接: SQL Group BY在新列中使用字符串
Awhile back I got some help with a specific query. Here's the link: SQL Group BY using strings in new columns
我的查询与此类似:
SELECT event_data, class_40_winner, class_30_winner
FROM events e
LEFT JOIN (SELECT result_event, name AS class_40_winner
FROM results
WHERE class = 40 AND position = 1) c40 ON e.id = c40.result_event
LEFT JOIN (SELECT result_event, name AS class_30_winner
FROM results
WHERE class = 30 AND position = 1) c30 ON e.id = c30.result_event
我现在在我的数据库中输入了足够的数据(22,000行),这个查询需要6秒才能完成。 (我的实际查询大于上面的内容,因为它现在有4个连接。)
I have now entered enough data in my database (22,000 rows) that this query is taking over 6 seconds to complete. (My actual query is bigger than the above, in that it now has 4 joins in it.)
我在查询中使用了Explain函数来查看。来自结果表的每个查询都会提取22,000行,所以这似乎是问题所在。
I used the "Explain" function on my query to take a look. Each of the queries from the "results" table is pulling in the 22,000 rows, so this seems to be the problem.
我做了一些研究,听起来像是我应该能够在结果表上索引相关列以帮助加快速度。但是当我这样做时,实际上我的查询速度降低了大约10秒。
I have done some research and it sounds like I should be able to INDEX the relevant column on the "results" table to help speed things up. But when I did that, it actually slowed my query down to about 10 seconds.
我可以采取哪些建议来改进此查询?
Any suggestions for what I can do to improve this query?
推荐答案
AFAIK,你正在转动你的数据,我认为使用 max(case ...)... group by
在旋转数据方面表现良好。
我建议您改用此查询:
AFAIK, you are pivoting your data and I think using max(case ...) ... group by
has good performance in pivoting data.
I can suggest you to use this query instead:
select event_date
, max(case when r.class = 40 then name end) `Class 40 Winner`
, max(case when r.class = 30 then name end) `Class 30 Winner`
from events e
left join results r on e.event_id = r.result_event and r.position = 1
group by event_date;
这篇关于MySQL Query占用时间超过6秒的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!