mysql - 在 where 子句中使用 OR 进行慢速查询

Question

请指教如何改进这个缓慢的查询。

SELECT response.`reasonid`
FROM response 
    INNER JOIN ACTION ON action.actionid = response.actionid 
WHERE 
    response.respdate BETWEEN 20160305 
    AND 20160905 
    AND 
    (
        (
         response.reasonid = 'Prospect Call' 
         AND response.typeid = '0'
         AND action.typeid = '9'
        ) 
    OR 
        (
         response.typeid = '1000'  
         AND action.typeid = '1'
        )
    ) 

有索引:

response.actionid/response.reasonid/response.typeid/action.typeid/response.respdate

解释结果:

 table   type   possible_keys                   key           key_len  ref               rows   Extra

ACTION   range  PRIMARY,idx_actiontypeid    idx_actiontypeid    5      \N               310617  Using where; Using index
response ref    idx_respdate2,idx_actionid, idx_actionid        5      ACTION.actionid    1         Using where
            idx_reasonid,idx_resptypeid

Answer 1

您的查询不应包含单独的索引，而应包含复合索引甚至覆盖索引。令我惊讶的是，没有其他人对此发表评论。我对索引提出以下建议

表格索引响应(typeid、respdate、reasoned、actionid) Action (actionid，typeid)

然后我将查询调整为使用 UNION 而不是 OR

select distinct
      r.reasonid
   from
      response r join action a
         on r.actionid = a.actionid
         AND a.typeid = 9
   where
          r.typeid = 0
      and r.respdate between 20160305 and 20160905
      and r.reasonid = 'Prospect Call'
union
select 
      r.reasonid
   from
      response r join action a
         on r.actionid = a.actionid
         AND a.typeid = 1
   where
          r.typeid = 1000
      and r.respdate between 20160305 and 20160905