mysql - 为什么查询这里不使用索引

Question

我的查询

      SELECT Info.InfoID,
      SessionInfo.SessionInfoID,
      SessionInfo.ANI,
      ANumber.ANumber,
      tmfInfo.PTime,
      tmfInfo.PTry,
      SessionInfo.CardID,
      tmfInfo.Status
 FROM tmfInfo,
      SessionInfo,
      ANumber ,
      ANumberLog,
      ANumberGroup,
      ANumberGroupLog
WHERE (tmfInfo.IVRSessionInfoID = SessionInfo.IVRSessionInfoID)
  AND (SessionInfo.ANumberLogID = ANumber.ANumberLogID)
  AND (ANumber.AccessNumberLogID = ANumberLog.ANumberLogID)
  AND (ANumberrLog.ANumberGroupID = ANumberGroup.ANumberGroupID)
  AND (ANumberGroup.ANumberGroupLogID = ANumberGroupLog.ANumberGroupLogID)
  AND (SessionInfo.SessionCallTime >= '2013-08-01 00:00:00'
  AND (SessionInfo.SessionCallTime <= '2013-08-01 23:59:59')
  AND (ANumberLog.IsDeleted = '0')
  AND (ANumberLog.IsActive = '1')
  AND (ANumberGroupLog.IsDeleted = '0')
  AND (ANumberGroupLog.IsActive = '1')
 ORDER BY SessionInfo.SessionCallTime,tmfInfo.PTime DESC;

解释查询结果

<小时/>

     id select_type table   type    possible_keys   key key_len ref  rows   Extra   
       1    SIMPLE  DtmfInfo    ALL SessionInfoID                 1728919   Using temporary; Using filesort 
      1 SIMPLE  SessionInfo eq_ref  PRIMARY,SessionCallTime,ANumberLogID    PRIMARY 8   IVRDtmfInfo.SessionInfoID   1   Using where 
      1 SIMPLE  Anumber ref AnumberLogID    AnumberLogID    5   SessionInfo.ANumberLogID    1   Using where 
     1  SIMPLE  AnumberLog  eq_ref  PRIMARY,AcumberGroupID,IsActive,IsDeleted   PRIMARY 4   SessionInfo.ANumberLogID    1   Using where 
      1 SIMPLE  AnumberGroup    eq_ref  PRIMARY,ANumberGroupLogID   PRIMARY 4   AnumberLog.ANumberGroupID   1       
      1 SIMPLE  AnumberGroupLog eq_ref  PRIMARY,IsActive,IsDeleted  PRIMARY 4   Using where Using where Using where 

<小时/>

我在 SessionInfoID 字段上有一个索引查询DtmfInfo表不使用索引....

任何帮助/解释都会有帮助。使用mysql 5.5.14

Answer 1

我认为你必须使用 FORCE INDEX :

查询将如下所示:

    SELECT Info.InfoID,          SessionInfo.SessionInfoID,          SessionInfo.ANI,          ANumber.ANumber,          tmfInfo.PTime,          tmfInfo.PTry,          SessionInfo.CardID,          tmfInfo.Status     FROM tmfInfo  FORCE INDEX ( index_name )   inner join  SessionInfo on  mfInfo.IVRSessionInfoID = SessionInfo.IVRSessionInfoID ,           ANumber ,          ANumberLog,          ANumberGroup,          ANumberGroupLog    WHERE       (SessionInfo.ANumberLogID = ANumber.ANumberLogID)      AND (ANumber.AccessNumberLogID = ANumberLog.ANumberLogID)      AND (ANumberrLog.ANumberGroupID = ANumberGroup.ANumberGroupID)      AND (ANumberGroup.ANumberGroupLogID = ANumberGroupLog.ANumberGroupLogID)      AND (SessionInfo.SessionCallTime >= '2013-08-01 00:00:00'      AND (SessionInfo.SessionCallTime