Optimisations, besoin d'aide [Résolu]

[lothar83fr]

Bonjour @ tous
J'ai besoin de votre aide pour optimiser mes requêtes ainsi que mon serveur mysql.
Je suis sous Ubuntu server 8.04.3 LTS et voici une partie de mon my.cnf:

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
 
#
# * Fine Tuning
#
key_buffer              = 16M
max_allowed_packet      = 16M
thread_stack            = 128K
thread_cache_size       = 8
#max_connections        = 100
#table_cache            = 64
#thread_concurrency     = 10
#
# * Query Cache Configuration
#
query_cache_limit       = 1M
query_cache_size        = 16M

Voici la structure de ma bdd:


Et voici une requête "type" de mon programme qui est lente sur environ 400 000 articles et 75 000 ordonnances:

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
 
SELECT
    o.*,
    DATE_FORMAT(o.date_enregistrement,'%H:%i:%s') AS heure,
    IFNULL(A.nbarticle,0) AS nbarticle,
    IFNULL(B.nbDelivre,0) AS nbDelivre,
    IFNULL(C.nbProduit,0) AS nbProduit,
    IFNULL(D.nbComplete,0) AS nbComplete,
    p.idgare
FROM ordonnance o
LEFT OUTER JOIN poste p USING (idposte)
LEFT OUTER JOIN article a USING (idordonnance)
LEFT OUTER JOIN produit pr USING (idproduit)
LEFT OUTER JOIN
(
    SELECT 
        idordonnance,
        count(idordonnance) AS nbarticle
    FROM article
    GROUP BY idordonnance
) AS A USING (idordonnance)
LEFT OUTER JOIN
(
    SELECT
        idordonnance,
        count(idordonnance) AS nbDelivre
    FROM article
    WHERE etat='Traite'
    GROUP BY idordonnance
) AS B USING (idordonnance)
LEFT OUTER JOIN
(
    SELECT 
        idordonnance,
        count(DISTINCT idproduit) AS nbProduit
    FROM article
    GROUP BY idordonnance
) AS C USING (idordonnance)
LEFT OUTER JOIN
(
    SELECT
        idordonnance,
        count(idordonnance) AS nbComplete
    FROM article
    WHERE etat='Complete'
    GROUP BY idordonnance
) AS D USING (idordonnance)

Je me retrouve souvent avec des requêtes imbriquées à cause des count(*)...
Voici le explain de la requête et c'est très mauvais:

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
 
+----+-------------+------------+--------+-----------------------+-----------------------+---------+------------------------+--------+---------------------+
| id | select_type | TABLE      | type   | possible_keys         | KEY                   | key_len | ref                    | rows   | Extra               |
+----+-------------+------------+--------+-----------------------+-----------------------+---------+------------------------+--------+---------------------+
|  1 | PRIMARY     | <derived5> | system | NULL                  | NULL                  | NULL    | NULL                   |      0 | const row NOT found |
|  1 | PRIMARY     | o          | ALL    | NULL                  | NULL                  | NULL    | NULL                   |  72614 |                     |
|  1 | PRIMARY     | p          | eq_ref | PRIMARY               | PRIMARY               | 4       | pharmax.o.idposte      |      1 |                     |
|  1 | PRIMARY     | a          | ref    | fk_article_ordonnance | fk_article_ordonnance | 5       | pharmax.o.idordonnance |      2 |                     |
|  1 | PRIMARY     | pr         | eq_ref | PRIMARY               | PRIMARY               | 4       | pharmax.a.idproduit    |      1 | USING INDEX         |
|  1 | PRIMARY     | <derived2> | ALL    | NULL                  | NULL                  | NULL    | NULL                   |  72225 |                     |
|  1 | PRIMARY     | <derived3> | ALL    | NULL                  | NULL                  | NULL    | NULL                   |  72225 |                     |
|  1 | PRIMARY     | <derived4> | ALL    | NULL                  | NULL                  | NULL    | NULL                   |  72225 |                     |
|  5 | DERIVED     | article    | INDEX  | NULL                  | fk_article_ordonnance | 5       | NULL                   | 397774 | USING WHERE         |
|  4 | DERIVED     | article    | INDEX  | NULL                  | fk_article_ordonnance | 5       | NULL                   | 397774 |                     |
|  3 | DERIVED     | article    | INDEX  | NULL                  | fk_article_ordonnance | 5       | NULL                   | 397774 | USING WHERE         |
|  2 | DERIVED     | article    | INDEX  | NULL                  | fk_article_ordonnance | 5       | NULL                   | 397774 | USING INDEX         |
+----+-------------+------------+--------+-----------------------+-----------------------+---------+------------------------+--------+---------------------+
12 rows IN SET (4.92 sec)

Merci d'avance pour vos conseils.

[ced]

Bonjour,

Essaie la requête suivante, qui ne fait qu'une seule jointure et une seule sous-requête et qui, je l'espère, donnera la même chose :

Code :

1
2
3
4
5
6
7
8
9
10
11
12
SELECT
	o.idordonnance, 
	DATE_FORMAT(o.date_enregistrement,'%H:%i:%s') AS heure, 
	p.idposte, 
	COUNT(a.idarticle) AS nbArticle,
	SUM(CASE WHEN a.etat = 'Traite' THEN 1 ELSE 0 END) AS nbDelivre,
	COUNT(DISTINCT a.idproduit) AS nbProduit,
	SUM(CASE WHEN a.etat = 'Complete' THEN 1 ELSE 0 END) AS nbComplete
FROM ordonnance o
LEFT OUTER JOIN poste p USING (idposte)
LEFT OUTER JOIN article a USING (idordonnance)
GROUP BY o.idordonnance, DATE_FORMAT(o.date_enregistrement,'%H:%i:%s'), p.idposte

Sinon, pour le nombre d'article, il est plus "lisible" de compter le nombre d'idarticle que le nombre d'idordonnance (même si ça donne le même résultat).

ced

[CinePhil]

Dans la structure de ta BDD, j'ai remarqué des clés primaires non numériques (notamment idrail). Ce sont de mauvaises clés qui nuisent aux performances.

Comment se fait-il que la table attente ne soit reliée à aucune autre ?

[lothar83fr]

Woot! Enorme, mille merci pour vos réponses le "SUM(CASE..." est super ingénieux, je pense pas que j'aurais trouvé tout seul . Pareil pour le "count(DISTINCT" je connaissais pas et ça va simplifier bon nombre de mes requêtes.

Par contre impossible d'utiliser les alias pour faire une nouvelle colonne sans passer par une requête imbriqué (SELECT [...] FROM (SELECT [...]) AS A...) Il y a une autre solution? De toute façon, il n'y a pas de jointures sur cette requête imbriquée (comme dans mon ex précédent) donc le temps de traitement ne doit pas beaucoup augmenter.

@Phil: effectivement mon idrail est un string. En réalité il s'agit d'un code hexa. Quel type choisir? Sachant que si je le met en integer, il faudra que je fasse appel à la fonction HEX() à chaque select ce qui me fait reprendre pas mal de code (pas insurmontable). Un conseil serrait le bien venu :p

Dernière chose, la base de données est utilisée par un logiciel codé en C++ ainsi que par des pages web. Lors d'un évènement sur la bdd, par ex une nouvelle entrée dans la table ordonnance, comment déclencher une action (côté C++ ou php) sans faire des requêtes en boucle pour interroger la table. J'avais pensé à utiliser un triger ainsi qu'une table de type memory qui contiendrait une liste de flag. On aurait 2 colones, une de type string et une autre de type boolean et dans notre ex, une entrée new_ordonnance qui passerait à true lors d'une insertion dans la bdd. On garde notre boucle de lecture qui est cette fois sur une table plus petite et en mémoire. Qu'en pensez-vous?

J'apprécie les efforts que vous faites pour me répondre, c'est quelque chose que moi-même je ne prend pas le temps de faire sur les forums...

[ced]

L'alias permet de "renommer" le résultat d'une requête. Effectivement, si on veut utiliser ce résultat dans une autre requête, il faut alors passer par une sous-requête.
Mais attention, les sous-requêtes dégradent rapidement les performances sous MySQL (ça commence à s'améliorer depuis la récente version 5.5).

Toutefois, il n'est pas toujours nécessaire de passer par une sous-requête. Tout dépend du problème qui se pose.

Pour ton problème de détection d'évènements, la mise en place de flags peut être une bonne solution. Il ne faudra pas oublier de remettre les flags à FALSE une fois l'info traitée côté PHP ou C++.

ced

[lothar83fr]

Pour la remonté d'évènements, je pense pas mettre un flag mais plutôt une date car il peut y avoir plusieurs clients en même temps et donc si l'un remet le flag à FALSE avant que l'autre ai mis à jours...
Pour moi, mon problème est résolu! Merci

[lothar83fr]

Toutefois, il n'est pas toujours nécessaire de passer par une sous-requête. Tout dépend du problème qui se pose.

Je passe par des sous-requêtes pour simplifier le code, exemple:

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
 
SELECT
            B.*,
            CASE
                WHEN B.libelle_reserve = 'Directe' AND B.gestion_reserve=false THEN
                    B.qte_a_prendre_theo
                WHEN B.libelle_reserve = 'Grossiste' AND B.gestion_reserve=false THEN
                    '0'
                ELSE
                    IF( B.qte_a_prendre_theo > B.reserve, B.reserve, B.qte_a_prendre_theo)
            END AS qte_a_prendre
        FROM
        (
            SELECT
                A.*,
                IF(A.SMU >0 AND A.SMU < A.SMT, A.SMU, A.SMT) AS qteMax,
                IF(IF(A.SMU >0 AND A.SMU < A.SMT, A.SMU, A.SMT) - A.stock >= 0, IF(A.SMU >0 AND A.SMU < A.SMT, A.SMU, A.SMT) - A.stock, 0) AS qte_a_prendre_theo
            FROM
            (
                SELECT
                    p.*,
                    DATE_FORMAT(p.date_creation,'%d/%m/%y - %hh%i') AS date_creationFormat,
                    tp.libelle AS libelle_type_produit,
                    reserve.libelle AS libelle_reserve,
                    tp.gestion_reserve,
                    COUNT(DISTINCT CASE WHEN s.date_sortie IS NULL AND s.date_entre IS NOT NULL AND r.statut!='OF' THEN idstock ELSE NULL END) AS stock,
                    COUNT(DISTINCT CASE WHEN r.statut!='OF' THEN r.idrail ELSE NULL END) AS nbRails,
                    COUNT(DISTINCT CASE WHEN r.statut!='OF' THEN r.idrail ELSE NULL END) * TRUNCATE(CASE WHEN r.statut!='OF' THEN tr.longueur/p.longueur ELSE 0 END,0) AS SMT,
                    COUNT(DISTINCT CASE WHEN r.statut!='OF' THEN r.idrail ELSE NULL END) * p.qte_max_rail AS SMU,
                    GROUP_CONCAT(DISTINCT CASE WHEN r.statut!='OF' THEN CONCAT(r.idmeuble,'/',r.traverse,'-',r.numero) ELSE NULL END SEPARATOR '<br/>') AS code_geo
                FROM
                    produit p
                LEFT OUTER JOIN stock s USING(idproduit)
                LEFT OUTER JOIN type_produit tp USING(idtype_produit)
                LEFT OUTER JOIN reserve USING(idreserve)
                LEFT OUTER JOIN rail r USING(idproduit)
                LEFT OUTER JOIN type_rail tr USING(idtype_rail)
                WHERE is_assigned=true
                GROUP BY idproduit
            ) AS A
        )B

Si j'avais pas fait des sous-requêtes, le code serrait illisible. Et je suis sur la version 5.0.51a for debian. Pour info la requête prend 7 secondes sur une machine standard avec bdd chargée.