Discussion :

[senacle]

Bonjour,

J'ai cette requête :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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 ?

[SQLpro]

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 +

[senacle]

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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.

[SQLpro]

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 +