Je développe une page personnalisée pour afficher les expéditions non facturées et je rencontre un problème de performance avec ma requête SQL.
Contexte :
Page listant les expéditions validées (statut 1) et fermées (statut 2) qui ne sont pas encore facturées
Gestion des factures d’acompte (type 3) : les expéditions restent « non facturables » même si la commande a une facture d’acompte
Calcul du montant HT proportionnel selon les quantités réellement expédiées
Problème : La requête est rapide avec e.fk_statut = 1 (0,3s) mais très lente avec e.fk_statut IN (1, 2) (>10s).
Diagnostic :
Pas d’index sur llx_expedition.fk_statut (vérifié avec SHOW INDEX)
La condition IN (1, 2) force un scan complet de la table
Ma requête actuelle :
SELECT e.rowid, e.ref, ec.fk_source as fk_commande, c.ref as ref_commande, s.nom as nom_societe, s.rowid as societe_id, SUM(ROUND((ed.qty / cd.qty) * cd.total_ht, 2)) as expedition_total_ht
FROM llx_expedition as e
LEFT JOIN llx_element_element as ec ON (ec.fk_target = e.rowid AND ec.sourcetype = 'commande' AND ec.targettype = 'shipping')
LEFT JOIN llx_commande as c ON ec.fk_source = c.rowid
LEFT JOIN llx_societe as s ON e.fk_soc = s.rowid
LEFT JOIN llx_expeditiondet as ed ON ed.fk_expedition = e.rowid
LEFT JOIN llx_commandedet as cd ON ed.fk_elementdet = cd.rowid
LEFT JOIN llx_element_element as ee ON ((ee.fk_target = e.rowid AND ee.sourcetype = 'facture' AND ee.targettype = 'shipping') OR (ee.fk_source = e.rowid AND ee.sourcetype = 'shipping' AND ee.targettype = 'facture'))
WHERE e.entity IN (1)
AND ee.rowid IS NULL
AND e.fk_statut IN (1, 2) -- Condition problématique
AND ed.qty > 0
AND cd.qty > 0
AND (c.facture = 0 OR c.facture IS NULL OR EXISTS (
SELECT 1 FROM llx_element_element ef
LEFT JOIN llx_facture f ON ef.fk_source = f.rowid
WHERE ef.fk_target = c.rowid AND ef.sourcetype = 'facture' AND ef.targettype = 'commande'
AND f.type = 3
))
GROUP BY e.rowid, e.ref, ec.fk_source, c.ref, s.nom, s.rowid
HAVING expedition_total_ht > 0
Existe-t-il une approche alternative pour optimiser cette requête sans index ?
à mon avis la bonne approche serait de remonter le besoin de l’index dans le coeur de dolibarr … on a déjà des index sur d’autres clés étrangères sur cette table (fk_soc, fk_user) ça semble normal d’avoir un index sur ce champ …
pouvez-vous essayer - sur une copie de la base de données, pas sur la prod - d’ajouter un index et voir le résultat ?
ALTER TABLE llx_expedition ADD INDEX idx_expedition_fk_statut (fk_statut);
selon votre retour je vous propose de me charger de faire la PR sur le git de dolibarr
ALTER TABLE llx_expedition ADD INDEX idx_expedition_fk_statut (fk_statut);
Résultats des tests :
L’index sur expedition.fk_statut fonctionne pour = 1 (238 lignes) mais MySQL refuse de l’utiliser pour IN (1,2) car cela représente 99,7% de la table (6654/6654 lignes)
Ma page est un peu plus rapide,il améliore les performances pour fk_statut = 1 (cas le plus fréquent). Pour fk_statut IN (1,2), les performances resteront limitées par la volumétrie, index ou pas?
SELECT e.rowid, e.ref, ec.fk_source as fk_commande, c.ref as ref_commande, s.nom as nom_societe, s.rowid as societe_id, SUM(ROUND((ed.qty / cd.qty) * cd.total_ht, 2)) as expedition_total_ht
FROM llx_expedition as e
LEFT JOIN llx_element_element as ec ON (ec.fk_target = e.rowid AND ec.sourcetype = 'commande' AND ec.targettype = 'shipping')
LEFT JOIN llx_commande as c ON ec.fk_source = c.rowid
LEFT JOIN llx_societe as s ON e.fk_soc = s.rowid
LEFT JOIN llx_expeditiondet as ed ON ed.fk_expedition = e.rowid
LEFT JOIN llx_commandedet as cd ON ed.fk_elementdet = cd.rowid
LEFT JOIN llx_element_element as ee ON ((ee.fk_target = e.rowid AND ee.sourcetype = 'facture' AND ee.targettype = 'shipping') OR (ee.fk_source = e.rowid AND ee.sourcetype = 'shipping' AND ee.targettype = 'facture'))
WHERE e.fk_statut IN (1, 2)
AND e.entity IN (1)
AND ee.rowid IS NULL
AND ed.qty > 0
AND cd.qty > 0
AND (c.facture = 0 OR c.facture IS NULL OR EXISTS (
SELECT 1 FROM llx_element_element ef
LEFT JOIN llx_facture f ON ef.fk_source = f.rowid
WHERE ef.fk_target = c.rowid AND ef.sourcetype = 'facture' AND ef.targettype = 'commande'
AND f.type = 3
))
GROUP BY e.rowid, e.ref, ec.fk_source, c.ref, s.nom, s.rowid
HAVING expedition_total_ht > 0
Un index sert à filtrer rapidement des lignes pour ne pas avoir à scanner toute la table. Il ne sert à rien dans le cas où cela représente 99,7% de la table et cela risque même d’être contre performant.
Vous pouvez tester le résultat avec en rajoutant FORCE INDEX (fk_statut) dans la requête.
Problème résolu Merci pour vos conseil, mais j’ai trouvé une solution d’optimisation qui fonctionne même sans index.
Après analyse avec EXPLAIN, le problème n’était pas uniquement le statut mais la structure même de la requête. L’index idx_expedition_fk_statut existe mais n’est pas utilisé car dans ma base, 100% des expéditions ont un statut 1 ou 2 (l’optimiseur considère qu’il n’y a rien à filtrer).
J’ai testé plusieurs variantes pour la condition sur le statut :
e.fk_statut IN ('1','2') (valeurs entre quotes) : aucune amélioration
e.fk_statut = 1 OR e.fk_statut = 2 : aucune amélioration
FORCE INDEX (idx_expedition_fk_statut) : l’index est utilisé mais même nombre de lignes examinées
Toutes ces variantes donnaient le même plan d’exécution avec scan complet (type: ALL).
Le problème n’était pas la condition sur le statut mais ailleurs dans la requête.
Le vrai goulot était le LEFT JOIN sur element_element combiné avec NOT EXISTS. Voici ma version optimisée :
-- AVANT : LEFT JOIN + NOT EXISTS (DEPENDENT SUBQUERY)
LEFT JOIN llx_element_element as ee ON (...)
WHERE ee.rowid IS NULL
-- APRÈS : Anti-jointure avec sous-requête matérialisée
LEFT JOIN (
SELECT fk_target as expedition_id FROM llx_element_element
WHERE sourcetype = 'facture' AND targettype = 'shipping'
UNION
SELECT fk_source as expedition_id FROM llx_element_element
WHERE sourcetype = 'shipping' AND targettype = 'facture'
) as facturees ON e.rowid = facturees.expedition_id
WHERE facturees.expedition_id IS NULL
Avant :DEPENDENT SUBQUERY exécutée pour chaque ligne (6641 × 23700 = ~157M opérations) Après : Sous-requête DERIVED matérialisée une fois (~47K opérations)
