Discussion :

[Xylane]

Bonjour,

Je travaille sur une base de donnée qui commence à prendre de l'ampleur, et j'ai noté qu'une requête était de plus en plus lente, alors qu'elle me semblait somme toute très simple.


Quelques informations concernant mes tables :
- user (clé primaire : id) contient dans les 130 000 enregistrements
- userRole (clé primaire : id, index : userId) contient aussi environ 130 000 enregistrements.
- Résultats retournés, environ 100 000.

Ce qui m'a étonné, c'est que la même requête, sans le ORDER BY user.id DESC s'exécute 80 fois plus vite...

Voici ce que j'obtiens en utilisant EXPLAIN :

- Requête initiale (temps d'exécution : 1.5 secondes) :

Code :

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

1
2
3
4
5
SELECT user.* 
FROM user 
LEFT OUTER JOIN user_role AS tRoles ON tRoles.userId = user.id 
WHERE tRoles.roleId = 5 
ORDER BY user.id DESC

Code :

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

1
2
3
id	select_type	table 		type 		possible_keys 		key 		key_len 	ref 				rows 		Extra 	
1	SIMPLE		tRoles 		ref 		userId,roleId 		roleId 		4 		const 				66483 		Using temporary; Using filesort
1	SIMPLE 		user 		eq_ref 		PRIMARY 		PRIMARY 	4 		amimalin2.com.tRoles.userId 	1

- Requête sans le ORDER BY (temps d'exécution : 0.0186 secondes) :

Code :

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

1
2
3
4
SELECT user.* 
FROM user 
LEFT OUTER JOIN user_role AS tRoles ON tRoles.userId = user.id 
WHERE tRoles.roleId = 5

Code :

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

1
2
3
id 	select_type 	table 		type 		possible_keys 		key 		key_len 	ref 				rows 		Extra 	
1 	SIMPLE 		tRoles 		ref 		userId,roleId 		roleId 		4 		const 				66483 	
1 	SIMPLE 		user 		eq_ref 		PRIMARY 		PRIMARY 	4 		amimalin2.com.tRoles.userId 	1

- Requête alternative que j'ai trouvé (temps d'exécution : 0.0029 secondes) :

Code :

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

1
2
3
4
5
SELECT user.*
FROM user
LEFT OUTER JOIN user_role AS tRoles ON tRoles.userId = user.id
WHERE tRoles.roleId = 5
ORDER BY tRoles.userId DESC

Code :

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

1
2
3
id 	select_type 	table 		type 		possible_keys 		key 		key_len 	ref 				rows 		Extra 	
1 	SIMPLE 		tRoles 		ref 		userId,roleId 		roleId 		4 		const 				66483 		Using where; Using filesort
1 	SIMPLE 		user 		eq_ref 		PRIMARY 		PRIMARY 	4 		amimalin2.com.tRoles.userId 	1

Ma question : Pourquoi la première requête (qui tri selon user.id qui est une clé primaire) prend autant de temps, comme si MySQL faisait le tri sur une colonne non-indexée ?

Merci d'avance !


Edit - Question légèrement changée suite à une erreur dans une des requêtes, et donc des résultats associés

[escartefigue]

Code :

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

1
2
3
4
5
SELECT user.* 
FROM user 
LEFT OUTER JOIN user_role AS tRoles ON tRoles.userId = user.id 
WHERE tRoles.roleId = 5 
ORDER BY user.id DESC

1ère remarque : vous faites une jointure OUTER en filtrant sur une colonne de la table OUTER, du coup ça revient à une jointure INNER !

2ème remarque : que le tri représente un coût n'est pas surprenant, de plus vous triez en descendant sur une colonne qui est incrémentée, et donc dont les valeurs vont plutôt dans un ordre croissant (même si la séquence n'est nullement garantie contrairement à ce qui est souvent cru).

[Xylane]

Bonjour escartefigue,

Et merci de ta réponse.

Je comprends parfaitement le fait que le tri représente un coût, mais ce coût devrait être minime (quelques millisecondes tout au plus). Là on parle d'une durée d'exécution 80 fois supérieure à la même requête sans la clause ORDER (plus d'une seconde supplémentaire).
Ce surcoût est largement trop élevé pour être normal, et d'ailleur la dernière requête citée (utilisant tRoles.userId au lieu de user.id) s'exécute en moins de trois milliseconde alors qu'elle contient elle aussi un tri dans le même order, et le même nombre de résultats.

Pour ce qui est du LEFT OUTER JOIN tu as raison mais là aussi, ça n'explique pas le surcoût exorbitant de la clause "ORDER BY".