Optimizing MySql Query Performance

Question

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

Did you try profiling the different parts of the php script? — mzedeler
– mzedeler, Commented Jun 22, 2013 at 6:45
I'm sorry, I'm not sure how one does that. Let me look into what profiling is. — asprin
– asprin, Commented Jun 22, 2013 at 6:46

score 1 · Accepted Answer · 2013-06-22 08:23:43Z

1

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

edited Jun 22, 2013 at 8:23

answered Jun 22, 2013 at 7:30

user359040

Sign up to request clarification or add additional context in comments.

3 Comments

asprin Over a year ago

Could you explain what this line does count(distinct case f31.rating when 1 then f31.parameter end) f31yes? Is it same as sum(if())?

user359040 Over a year ago

Yes and no - in this case, it should have the same effect as the equivalent sum(if()) expression. count(distinct ...) returns the number of distinct values in the rest of the expression, within the group excluding nulls; case expression when value then result else other result end is an expression that evaluates to result when expression=value, otherwise it evaluates to other result (or NULL if no else clause is included). So f31yes is effectively counting the number of different f31.parameter values for each job_number and set_no, where rating is 1.

user359040 Over a year ago

(Added GROUP BY clause to query.)

Community · Accepted Answer · 2017-05-23 12:11:14Z

0

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.

edited May 23, 2017 at 12:11

CommunityBot

11 silver badge

answered Jun 22, 2013 at 7:46

kevinandrada

4992 gold badges5 silver badges15 bronze badges

Collectives™ on Stack Overflow

Optimizing MySql Query Performance

job_master

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

f31

f32

2 Answers 2

3 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

job_master

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

f31

f32

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related