如何提高查询执行的速度?

I have the following query:

我有以下查询:

SELECT SUM(sureness) FROM tweet WHERE ID in
(select DataitemID from entity_dataitem_relation  where EpochID IN 
(SELECT ID FROM epoch WHERE StartDateTime>='2013-11-01' AND EndDateTime<='2013-11-31')AND
    DataitemType=3) ;

And I indexed DataitemID in entity_dataitem_relation table to increase the speed. I even indexed EpochID to help increasing speed but still this query is very slow and it takes 2 min to be executed(it is noteworthy that I entered in a project in the middle of that so I have to continue with what others did so I dont have much flexibility in terms of design)

我在entity_dataitem_关系表中对DataitemID进行了索引，以提高速度。我甚至索引EpochID帮助增加这个查询速度,但仍然非常缓慢,需要2分钟执行(值得注意的是我进入一个项目中,所以我必须继续与别人这样做,我不有很多灵活性的设计)

NB: the following part is very fast:

NB:下面的部分非常快:

SELECT DataitemID from entity_dataitem_relation  where EpochID IN 
(SELECT ID FROM epoch WHERE StartDateTime>='2013-11-01' AND EndDateTime<='2013-11-31');

Another thing : even when I replace sureness with ID which is prinmary key of the table it still takes that much time what should I do ? Is there anything that I can do to improve speed?

还有一件事:即使我用ID来代替sureness，我还是用了很多时间，我该怎么做呢?我能做些什么来提高速度吗?

1 个解决方案

#1

To try to reduce table scanning, you can rewrite this query using JOINS:

为了减少表扫描，您可以使用连接重写此查询:

SELECT SUM(t.sureness)
FROM tweet t
JOIN entity_dataitem_relation edr
  ON edr.DataitemID = t.ID
    AND edr.DataitemType = 3
JOIN epoch e
  ON e.ID = edr.EpochID
    AND e.StartDateTime >= '2013-11-01'
    AND e.EndDateTime <= '2013-11-31'

Add the following covering indexes:

添加以下覆盖索引:

tweet(ID, sureness)
entity_dataitem_relation(DataitemID, DataitemType, EpochID)
epoch(ID, StartDateTime, EndDateTime)

MySQL will probably still scan through all the rows in the index on tweet, so the more records you have in the tweet table, the slower this will be.

MySQL可能仍然会在tweet上浏览索引中的所有行，因此在tweet表中有越多的记录，就会越慢。

#1