Optimisation count et join

Bonjour,

J'ai cette requête :

Code:

---

1 2 3 4 5

SELECT COUNT(*) AS NB, ID_LOTERIE FROM BILLET JOIN PARTICIPE ON PARTICIPE.ID_BILLET = BILLET.ID_BILLET WHERE PARTICIPE.ID_LOTERIE = 29 AND VALIDITE_BILLET = 1;

---

Elle prend en moyenne 500 ms.

J'ai ajouté un index sur VALIDITE_BILLET, on descend à 310-320ms.
Mais je trouve que c'est encore long.

Si je fais seulement

Code:

---

1 2 3 4 5

SELECT ID_LOTERIE FROM BILLET JOIN PARTICIPE ON PARTICIPE.ID_BILLET = BILLET.ID_BILLET WHERE PARTICIPE.ID_LOTERIE = 29 AND VALIDITE_BILLET = 1;

---

J'arrive à 35ms.

Voici l'explain :

Code:

---

1 2 3 4

id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE PARTICIPE NULL ref PRIMARY,FK_PARTICIPE3,index_loterie PRIMARY 4 const 93715 100.00 Using index 1 SIMPLE BILLET NULL eq_ref PRIMARY,index_validite PRIMARY 4 PARTICIPE.ID_BILLET 1 99.04 Using where

---

Index sur la table billet

Code:

---

1 2 3 4 5

Nom index Type Unique Compressé Colonne Cardinalité Interclassement Null PRIMARY BTREE Oui Non ID_BILLET 359948 A Non FK_VALIDE BTREE Non Non ID_JOUEUR 1739 A Oui index_validite BTREE Non Non VALIDITE_BILLET 2 A Non

---

index de la table participe

Code:

---

1 2 3 4 5 6 7 8

Nom index Type Unique Compressé Colonne Cardinalité Interclassement Null PRIMARY BTREE Oui Non ID_LOTERIE A Non ID_PARIEUR A Non ID_BILLET 359948 A Non FK_PARTICIPE2 BTREE Non Non ID_PARIEUR 609 A Non FK_PARTICIPE3 BTREE Non Non ID_BILLET 359948 A Non index_loterie BTREE Non Non ID_LOTERIE 13 A Non

---

Une idée pour diminuer encore le temps de traitement avec COUNT ?

Aucun de ces index ne sert votre requête !
Vous avez des index monocolonne alors que votre requête utilise 2 colonne pour BILLET et 2 autres pour PARTICIPE

Créez les bons index,et vous verrez que cela marchera mieux....
par exemple :

• PARTICIPE => (ID_LOTERIE, ID_BILLET);
• BILLET => (VALIDITE_BILLET, ID_BILLET);

Apprenez à créer les bons index à l'aide de mon cours :
https://sqlpro.developpez.com/cours/quoi-indexer
https://blog.developpez.com/sqlpro/p..._oui_mais_quoi
https://blog.developpez.com/sqlpro/p...ut_sur_l_index
https://blog.developpez.com/sqlpro/p...sql_sargable_c

A +



A +

Citation:

---

Envoyé par SQLpro

Apprenez à créer les bons index à l'aide de mon cours :
https://sqlpro.developpez.com/cours/quoi-indexer
https://blog.developpez.com/sqlpro/p..._oui_mais_quoi
https://blog.developpez.com/sqlpro/p...ut_sur_l_index
https://blog.developpez.com/sqlpro/p...sql_sargable_c

---

J'ai bien compris l'intérêt de mettre des index sur plusieurs colonnes à la fois, avec l'importance de l'ordre des colonnes.

J'ai fait l'exercice d'ajouter les index progressivement, avec cette requête :

Code:

---

1 2 3 4 5

SELECT COUNT(*) AS NB, ID_LOTERIE FROM BILLET JOIN PARTICIPE ON PARTICIPE.ID_BILLET = BILLET.ID_BILLET WHERE PARTICIPE.ID_LOTERIE = 29 AND VALIDITE_BILLET = 1;

---

Je mets le résultat de l'explication de la requête.
Le temps indiqué est celui du traitement global que je fais, incluant la requête.

Sans index - 2,8 secondes

Code:

---

1 2 3

id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE BILLET NULL ALL PRIMARY NULL NULL NULL 361131 10.00 Using where 1 SIMPLE PARTICIPE NULL ref PRIMARY,FK_PARTICIPE3 FK_PARTICIPE3 8 BILLET.ID_BILLET,const 1 100.00 Using index

---

index table billet : validite - 1.1 secondes

Code:

---

1 2 3

id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE PARTICIPE NULL ref PRIMARY,FK_PARTICIPE3 PRIMARY 4 const 179778 100.00 Using index 1 SIMPLE BILLET NULL eq_ref PRIMARY,billet_validite PRIMARY 4 PARTICIPE.ID_BILLET 1 99.04 Using where

---

index table billet : validite/id_billet - 1 seconde

Code:

---

1 2 3

id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE PARTICIPE NULL ref PRIMARY,FK_PARTICIPE3 PRIMARY 4 const 179778 100.00 Using index 1 SIMPLE BILLET NULL eq_ref PRIMARY,billet_validite PRIMARY 4 PARTICIPE.ID_BILLET 1 99.45 Using where

---

index table billet : validite/id_billet - index table participe : id_loterie - 1,1 secondes

Code:

---

1 2 3

id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE PARTICIPE NULL ref PRIMARY,FK_PARTICIPE3,ID_LOTERIE ID_LOTERIE 4 const 179778 100.00 Using index 1 SIMPLE BILLET NULL eq_ref PRIMARY,billet_validite PRIMARY 4 PARTICIPE.ID_BILLET 1 99.45 Using where

---

index table billet : validite/id_billet - index table participe : id_loterie/id_billet - 1,1 secondes

Code:

---

1 2 3

id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE PARTICIPE NULL ref PRIMARY,FK_PARTICIPE3,ID_LOTERIE_ID_BILLET ID_LOTERIE_ID_BILLET 4 const 179778 100.00 Using index 1 SIMPLE BILLET NULL eq_ref PRIMARY,billet_validite PRIMARY 4 PARTICIPE.ID_BILLET 1 99.45 Using where

---

index table billet : validite/id_billet et supression de l'index primary - index table participe : id_loterie/id_billet - 0,5 secondes

Code:

---

1 2 3

id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE PARTICIPE NULL ref PRIMARY,FK_PARTICIPE3,ID_LOTERIE_ID_BILLET ID_LOTERIE_ID_BILLET 4 const 179778 100.00 Using index 1 SIMPLE BILLET NULL eq_ref VALIDITE_BILLET VALIDITE_BILLET 5 const, PARTICIPE.ID_BILLET 1 100.00 Using index

---

On voit que la mise en place de l'index billet_validite sur la colonne VALIDITE seule permet un gain x2 environ. Ca correspond à l'aspect "sargable" si j'ai bien compris.
La suppression de l'index primary sur la table billet permet à nouveau un gain x2, en "forçant" l'utilisation de l'index billet_validite sur les colonnes validite/id_billet.
En remettant l'index PRIMARY sur la table billet avec les colonnes id_billet/validite et en conservant l'index billet_validite, on gagne quelques millisecondes encore.

L'ajout des autres index sur la table participe ne changent rien.

Ceci dit, MySQL n'est absolument pas un SGBD fait pour les grosses volumétries.... En pratique à partir de quelques dizaines de Go et quelques millions de lignes il est totalement dépassé par rapport à SQL Server, Oracle ou DB2...
Si vous voulez du libre, PostGreSQL restent un bon choix pour des volumétries moyennes...

A +