在MySQL上建立索引

Question

我有一个使用子查询的批量查询。当我在开发服务器上运行我的查询时，它工作得很好，但是当我在实时服务器上尝试它时，查询花费了太多的时间来产生输出。我想是因为直播服务器上的大数据。谁能帮助我如何在MySQL上索引查询，以便它将减少执行时间。

下面是我的问题：

SELECT prd.fldemployeeno AS Empno,
       (SELECT fldemployeename FROM tblprofile prf WHERE prf.fldemployeeno = prd.fldemployeeno LIMIT 0,1) AS Empname,
       '01' AS `Week`,
       COUNT(DISTINCT isAud.fldid) AuditedFiles, 
       COUNT(qua.seqid) ErrorCount, 
       COUNT(DISTINCT qua.fldid) OrdersWithError
  FROM tbldownloadITL dwn 
 INNER JOIN tblproductionITL prd 
    ON dwn.fldid = prd.fldglobalid
 INNER JOIN (SELECT p.fldemployeeno,fldglobalid,p.fldstarttime,COALESCE(q.fldstarttime,p.fldstarttime) `AuditDate`
               FROM tblproductionitl p
               LEFT JOIN tblqualityaudit q
                 ON p.fldemployeeno=q.fldemployeeno
                AND p.fldstarttime=q.fldprodstarttime
                AND p.fldglobalid=q.fldid
              WHERE p.fldprojectgroup='PROJGROUP') temp
    ON prd.fldglobalid=temp.fldglobalid
   AND prd.fldemployeeno=temp.fldemployeeno
   AND prd.fldstarttime=temp.fldstarttime
 INNER JOIN tblisauditedITL isAud 
 USING (fldid)
  LEFT JOIN tblqualityaudit qua 
    ON qua.fldid = dwn.fldid
   AND qua.fldbusunit = dwn.fldbusunit
   AND qua.fldprojectGroup = dwn.fldprojectGroup
   AND qua.fldemployeeno = prd.fldemployeeno
   AND qua.fldprodstarttime = prd.fldstarttime
   AND qua.flderrorstatus != 'NOT ERROR' 
  LEFT JOIN tblerrorcategory 
 USING (flderrorcategoryid) 
  LEFT JOIN tblerrortypes
 USING (flderrortypeid)
 WHERE dwn.fldbusunit = 'BUSUNIT'
   AND dwn.fldprojectGroup = 'PROJGROUP'
   AND temp.AuditDate BETWEEN '2011-07-29 00:00:00' AND '2011-07-29 23:59:59'
 GROUP BY prd.fldemployeeno 
 ORDER BY Empname

下面是对查询的描述：

​

​

Answer 1

如果你有权限的话，我建议你在你的服务器上安装Sphinx。这样，你就可以在指尖上拥有一个索引资源，以便进行极快的搜索，除此之外，你还可以添加所谓的“增量”索引的执行，以允许实时更新你的mysql数据库。它是高度可定制的。希望这能帮到你。

http://sphinxsearch.com/