CODEWITHSUNDEEP
phpmysqlsqlperformancesimplify
TigeR
TigeR

Reputation: 31

Speeding up SQL query that compute rates

I've to compute a conformity rate and also to keep the total of deliveries, all that for each carrier (transporteur) in SQL. Here is the query I'd tailored to answer that need and it does that successfully, but it's taking about 5 minutes to execute (knowing that "LIVRAISON" table have about 350.000 entries):

SELECT idTrans AS id,
nomTrans,
(COUNT(codeSt)
    / (SELECT COUNT(*) 
       FROM LIVRAISON 
           NATURAL JOIN TOURNEE
       WHERE idTrans=id
           AND DateTrn = DATE_SUB(SYSDATE(), INTERVAL 1 DAY)
       )
) AS Taux,
(SELECT COUNT(*)
 FROM LIVRAISON
     NATURAL JOIN TOURNEE
 WHERE idTrans=id
     AND DateTrn = DATE_SUB(SYSDATE(), INTERVAL 1 DAY)
) AS Total 
FROM LIVRAISON
    NATURAL JOIN TOURNEE
    NATURAL JOIN TRANSPORTEUR
WHERE "{status_type}"
    AND DateTrn = DATE_SUB(SYSDATE(), INTERVAL 1 DAY)
GROUP BY idTrans

(status_type is a IN condition added in PHP.) How can I speed up this kind of query (and simplify it by the way) to a few seconds only?

Upvotes: 1

Views: 53

Answers (2)

TigeR
TigeR

Reputation: 31

I reworked scaisEdge answer (that execute in time but gives rates all equal to 1) to make it usable:

SELECT TOURNEE.idTrans, nomTrans, ( t.tot_typeSt/COUNT(*) ) AS Taux,
COUNT(*) AS Total
FROM LIVRAISON NATURAL JOIN TOURNEE NATURAL JOIN TRANSPORTEUR
INNER JOIN ( SELECT TOURNEE.idTrans, COUNT(codeSt) AS tot_typeSt
    FROM LIVRAISON  NATURAL JOIN TOURNEE
WHERE {$typeStatut} AND DATE_SUB(SYSDATE(), INTERVAL 1 DAY)
GROUP BY TOURNEE.idTrans ) t ON t.idTrans = TOURNEE.idTrans WHERE DATE_SUB(SYSDATE(), INTERVAL 1 DAY)
GROUP BY idTrans

Thanks, I'll remember using INNER JOIN for that.

Upvotes: 0

ScaisEdge
ScaisEdge

Reputation: 133380

Looking to your code you could avoid the select for column values using a subselect in join eg:

select DISTINCT LIVRAISON.idTrans AS id
  , TRANSPORTEUR.nomTrans
  , t.my_rate
  , t.my_count
FROM LIVRAISON 
NATURAL JOIN TOURNEE
NATURAL JOIN TRANSPORTEUR
INNER JOIN  (
SELECT idTrans, COUNT(codeSt)/COUNT(*) my_rate,  COUNT(*) my_count
FROM LIVRAISON 
NATURAL JOIN TOURNEE
WHERE idTrans=id 
AND DateTrn = DATE_SUB(SYSDATE(), INTERVAL 1 DAY)
GROUP BY idTrans ) t ON t.idTrans = LIVRAISON.idTrans 
WHERE "{status_type}"
AND DateTrn = DATE_SUB(SYSDATE(), INTERVAL 1 DAY)

could be that you can also avoid the IN clause using another inner join

Upvotes: 1

Related Questions