StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POIndexing on MySQL
text
Body
copied!<p>I have a bulk query with subquery. My query works fine when I run it on development server, but when I've try it pn the live server, the query takes too much time to produce an output. I think it's because of a big data on the live server. Can anyone help me on how to index query on MySQL so that it will lessen the time execution.</p> <p>Here is my query:</p> <pre><code>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 </code></pre> <p>Here is also the description of the query:</p> <p><img src="https://i.stack.imgur.com/XgExC.jpg" alt="enter image description here"></p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload