CODEWITHSUNDEEP
mysqloptimization
asprin
asprin

Reputation: 9823

Optimizing MySql Query Performance

I'm pulling data from 4 tables which are:

job_master

job_numberclientcol3col4
123                45      blah blah 
456                12      blah blah 

accompaniment_common (f31 and f32 will contain 3 sets as there 3 in this table)

job_numberset_nodate_audit   column 4column 5column 3
123                1           2013-04-23   blah blah   blah blah  blah blah   
456                2           2013-04-13   blah blah   blah blah  blah blah   
123                3           2013-04-10   blah blah   blah blah  blah blah   

f31

job_numberparameterratingcommentsset_no
123                1                  2        blah blah ble1           
123                2                  2        blah blah ble1           
123                3                  1        blah blah ble1           
456                1                  1        blah blah ble2           
456                2                  1        blah blah ble2           
456                3                  2        blah blah ble2           
123                1                  2        blah blah ble3           
123                2                  2        blah blah ble3           
123                3                  1        blah blah ble3           

f32

job_numberparameterratingcommentsset_no
123                1                  3        blah blah ble1           
123                2                  1        blah blah ble1           
123                3                  1        blah blah ble1           
456                1                  2        blah blah ble2           
456                2                  3        blah blah ble2           
456                3                  1        blah blah ble2           
123                1                  2        blah blah ble3           
123                2                  2        blah blah ble3           
123                3                  1        blah blah ble3           

The query that I'm using is:

SELECT * FROM 
   (SELECT job_number, set_no, SUM(IF(rating=1,1,0))  as f31yes, 
   SUM(if(rating=2,1,0))  as f31no FROM f31 GROUP BY job_number, set_no) x 

   JOIN 

   (SELECT job_number, set_no, SUM(IF(rating=1,1,0)) as f32yes, 
   SUM(IF(rating=2,1,0)) as f32no FROM f32 GROUP BY job_number, set_no) y       
   ON (x.job_number = y.job_number and x.set_no = y.set_no)

   JOIN 

   (SELECT date_audit, job_number, set_no FROM accompaniment_common) z
   ON (x.job_number = z.job_number and x.set_no = z.set_no) 

   JOIN

   (SELECT job_number, client FROM job_master) jm
   ON (z.job_number = jm.job_number)

   WHERE z.date_audit >= '2013-04-01' AND z.date_audit < '2013-05-01'
   AND jm.client = 45
   HAVING ((f31yes+f32yes)/(f31yes+f32yes+f31no+f32no)) >= 0.9

When I run this query in phpMyAdmin, the results are obtained within 2-3 seconds. This seems like quick retrieval but when I put this inside a PHP file and execute it, it takes like 5-6 minutes to complete.

Of course I run it in a loop for different clients such as:

$clients = array(45,10,12,13,14,25,60,144);
$total = array();
foreach($clients as $client)
{
   $q = mysql_query(// the above query goes here);
   $numrows = mysql_num_rows($q);
   $total[$client] = $numrows;
}
echo '<pre>';
print_r($array);
echo '</pre>';

If I go by the time taken in phpMyAdmin, the whole process should be complete within 30 seconds, but it doesn't. This is baffling as I think I'm using the query in an appropriate way. Any suggestions?

PS : Yes, I'm aware that mysql_* are deprecated, but we took over this project from another company and don't have enough time to switch to PDO or mysqli

Upvotes: 0

Views: 75

Answers (2)

user359040
user359040

Reputation:

Instead of running multiple queries (one per client), try running a single query for all required clients - like:

SELECT jm.job_number, 
       jm.client,
       ac.date_audit, 
       ac.set_no,
       count(distinct case f31.rating when 1 then f31.parameter end) f31yes,
       count(distinct case f31.rating when 2 then f31.parameter end) f31no,
       count(distinct case f32.rating when 1 then f32.parameter end) f32yes,
       count(distinct case f32.rating when 2 then f32.parameter end) f32no
FROM job_master jm
JOIN accompaniment_common ac ON jm.job_number = ac.job_number
JOIN f31 ON ac.job_number = f31.job_number and ac.set_no = f31.set_no
JOIN f32 ON ac.job_number = f32.job_number and ac.set_no = f32.set_no
WHERE ac.date_audit >= '2013-04-01' AND ac.date_audit < '2013-05-01' 
  AND jm.client in (45,10,12,13,14,25,60,144)
GROUP BY jm.job_number, ac.set_no
HAVING (f31yes+f32yes) / (f31yes+f32yes+f31no+f32no) >= 0.9

Upvotes: 1

kevinandrada
kevinandrada

Reputation: 499

Add index to your columns that are used in the JOINs.

ALTER TABLE f31 ADD INDEX (job_number);
ALTER TABLE f31 ADD INDEX (set_no);
ALTER TABLE f32 ADD INDEX (job_number);
ALTER TABLE f32 ADD INDEX (set_no);
ALTER TABLE accompaniment_common ADD INDEX (job_number);
ALTER TABLE accompaniment_common ADD INDEX (set_no);
ALTER TABLE job_master ADD INDEX (job_number);

For more info about database indexing, read here.

Upvotes: 0

Related Questions