MySQL Query占用时间超过6秒

Awhile back I got some help with a specific query. Here's the link: SQL Group BY using strings in new columns

一段时间后，我得到了一些特定查询的帮助。这是链接：SQL Group BY在新列中使用字符串

My query looks similar to this:

我的查询看起来与此类似：

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

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.)

我现在在我的数据库中输入了足够的数据（22,000行），这个查询需要6秒才能完成。（我的实际查询大于上面的内容，因为它现在有4个连接。）

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.

我在查询中使用了“Explain”函数来查看。来自“结果”表的每个查询都会拉入22,000行，所以这似乎是个问题。

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.

我做了一些研究，听起来我应该能够在“结果”表中索引相关列以帮助加快速度。但是当我这样做时，它实际上将我的查询减慢到大约10秒。

Any suggestions for what I can do to improve this query?

有什么建议我可以做些什么来改善这个查询？

3 个解决方案

#1

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:

AFAIK，您正在转动您的数据，我认为使用max（case ...）... group by在数据透视方面具有良好的性能。我建议你改用这个查询：

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;

[SQL Fiddle Demo]

[SQL小提琴演示]

#2

Try this query:

试试这个查询：

SELECT
  e.event_date,
  r1.name as class_40_winner,
  r2.name as class_30_winner
FROM
  events e,
  results r1,
  results r2
WHERE
  r1.class = 40 AND
  r2.class = 30 AND
  r1.position = 1 AND
  r2.position = 1 AND
  r1.result_event = e.id AND
  r2.result_event = e.id

#3

SELECT e.event_data
     , r.class
     , r.name winner
  FROM events e
  JOIN results r
     ON r.result_event = e.id 
 WHERE  class IN (30,40) 
   AND position = 1

The rest of this problem is a simple display issue, best resolved in application code.

此问题的其余部分是一个简单的显示问题，最好在应用程序代码中解决。

#1