Majestic Majestic - 5 months ago 7
SQL Question

Improve a SQL query

I retrieved a project which takes a long time to run because of a SQL query.

I'd like to know if you know a way to improve it.
At least if there is a way to improve it.

From what I know, the query gets back 1500 lines and makes a SUM and COUNT from other tables.

SELECT
p.id,
p.datecreate,
p.title,
p.address,
p.address2,
p.code_postal,
p.ville,
p.description,
p.description_admin,
p.idstatut,
p.idstatut_admin,
p.reference,
p.star,
p.logo,
p.deleted,
cc.value as starcolor,
p.idsociete,
soc.nom as societe_nom,
s.titlestatut,
s.fontcolor,
s.label as label,
sa.titlestatut as titlestatut_admin,
sa.fontcolor as fontcolor_admin,
sa.label as label_admin,
(SELECT SUM(nbr) FROM plans as cplans WHERE cplans.idprojets = p.id) as count_commandes,
(SELECT count(id) FROM files as cfiles WHERE cfiles.idprojets = p.id AND cfiles.folder = '1' AND cfiles.bat_valid = '0') as count_averifier,
(SELECT count(rowid) FROM files_modif_title as cfmt WHERE cfmt.idprojets = p.id AND cfmt.statut = '7' AND cfmt.hide = '0') as count_modif_nohide,
(SELECT count(rowid) FROM files_modif_title as cfmt WHERE cfmt.idprojets = p.id AND cfmt.statut = '7' AND cfmt.hide IN (1)) as count_modif_hide,
(SELECT count(id) FROM files as cfiles WHERE cfiles.idprojets = p.id AND cfiles.folder = '2' AND cfiles.bat_valid = '0') as count_bat_attente,
(SELECT count(id) FROM files as cfiles WHERE cfiles.idprojets = p.id AND cfiles.folder = '2' AND cfiles.bat_valid = '1') as count_bat_valider
FROM projets as p INNER JOIN societe AS soc ON p.idsociete = soc.id
INNER JOIN statuts AS s ON p.idstatut = s.id
INNER JOIN statuts AS sa ON p.idstatut_admin = sa.id
LEFT JOIN const AS cc ON cc.name = p.star AND cc.parent = 'star'
WHERE p.idstatut IN (3)
AND p.deleted = 0
GROUP BY p.id
ORDER BY p.datecreate DESC


Thank you guys !

Edit -----

Here what I did, could it be better ?

SELECT
p.id,
p.datecreate,
p.title,
p.address,
p.address2,
p.code_postal,
p.ville,
p.description,
p.description_admin,
p.idstatut,
p.idstatut_admin,
p.reference,
p.star,
p.logo,
p.deleted,
cc.value as starcolor,
p.idsociete,
soc.nom as societe_nom,
s.titlestatut,
s.fontcolor,
s.label as label,
sa.titlestatut as titlestatut_admin,
sa.fontcolor as fontcolor_admin,
sa.label as label_admin,
CC.count_commandes,
CA.count_averifier,
CMN.count_modif_nohide,
CMH.count_modif_hide,
CBA.count_bat_attente,
CBV.count_bat_valider
FROM
projets as p
INNER JOIN societe AS soc
ON p.idsociete = soc.id
INNER JOIN statuts AS s
ON p.idstatut = s.id
INNER JOIN statuts AS sa
ON p.idstatut_admin = sa.id
LEFT JOIN const AS cc
ON cc.name = p.star
AND cc.parent = 'star'
LEFT JOIN (

SELECT idprojets, SUM(nbr) as count_commandes
FROM plans
GROUP BY idprojets
) AS CC
ON p.id = CC.idprojets
LEFT JOIN (

SELECT idprojets, COUNT(*) AS count_averifier
FROM files
GROUP BY idprojets
WHERE cfiles.folder = 1 AND cfiles.bat_valid = 0
) AS CA
ON p.id = CA.idprojets
LEFT JOIN (

SELECT idprojets, COUNT(*) as count_modif_nohide
FROM files_modif_title
WHERE statut = 7 AND hide = 0
GROUP BY idprojets
) AS CMN
ON p.id = CMN.idprojets
LEFT JOIN (

SELECT idprojets, COUNT(*) as count_modif_hide
FROM files_modif_title
WHERE statut = 7 AND hide = 1
GROUP BY idprojets
) AS CMH
ON p.id = CMH.idprojets
LEFT JOIN (

SELECT idprojets, COUNT(*)
FROM files
WHERE folder = 2 AND bat_valid = 0
GROUP BY idprojets
) AS CBA
ON p.id = CBA.idprojets
LEFT JOIN (

SELECT idprojets, COUNT(*)
FROM files
WHERE folder = 2 AND bat_valid = 1
GROUP BY idprojets
) AS CBV
ON p.id = CBV.idprojets
WHERE
p.idstatut IN (3)
AND p.deleted = 0
GROUP BY p.id
ORDER BY p.datecreate DESC;


Thanks to what you said, the main problem was the 6 SELECT are embedded in the SELECT clause. These are evaluated for each record put together by the application so it executes 1500 x 6 = 9000 queries!
By doing this I had 9001 queries and now only 7 since subqueries are evaluated only once at runtime. Is that correct ?

Answer

This is the part you defiantly want to improve:

(SELECT SUM(nbr) FROM plans as cplans WHERE cplans.idprojets = p.id) as count_commandes,
(SELECT count(id) FROM files as cfiles WHERE cfiles.idprojets = p.id AND cfiles.folder = '1' AND cfiles.bat_valid = '0') as count_averifier,
(SELECT count(rowid) FROM files_modif_title as cfmt WHERE cfmt.idprojets = p.id AND cfmt.statut = '7' AND cfmt.hide = '0') as count_modif_nohide,
(SELECT count(rowid) FROM files_modif_title as cfmt WHERE cfmt.idprojets = p.id AND cfmt.statut = '7' AND cfmt.hide IN (1)) as count_modif_hide,
(SELECT count(id) FROM files as cfiles WHERE cfiles.idprojets = p.id AND cfiles.folder = '2' AND cfiles.bat_valid = '0') as count_bat_attente,
(SELECT count(id) FROM files as cfiles WHERE cfiles.idprojets = p.id AND cfiles.folder = '2' AND cfiles.bat_valid = '1') as count_bat_valider

You can use conditional aggregation for this and join the tables only once:

count(nbr) as..,
count(CASE WHEN cfiles.folder = '1' and cfiles.bat_valid = '0' then id END) as ..,
count(CASE WHEN cfiles.folder = '2' and cfiles.bat_valid = '0' then id END) as ..,
count(CASE WHEN cfiles.folder = '2' and cfiles.bat_valid = '1' then id END) as ..,
........

Add the joins

JOIN files cfiles
 ON(cfiles.idprojets = p.id)

Do exactly the same for files_modif_title