optimisation de requête

Version imprimable

Bonjour,

J'ai une requête qui me pose problème et que je n'arrive pas à optimiser.
La voici :

Code:

1
2
3
4
5
6
7
8
9
 
SELECT *
FROM  envois, task , messages, sites 
 
WHERE envois.idEnvoyeur = 1  AND envois.id = task.idEnvoi AND ( envois.status = 'ENV' OR (envois.status = 'PRG' AND envois.date_lance <= CURRENT_TIMESTAMP))
AND envois.idMessage = messages.id AND messages.idSite = sites.id 
 
ORDER BY envois.priority desc, envois.id asc, task.id asc
LIMIT 0,150

voila ce que donne l'explain

Code:

1
2
3
4
5
6
7
8
 
id 	select_type 	table 		type 	possible_keys 		key 		key_len 	ref 			rows 	Extra
 
1 	SIMPLE 		task 		ALL 	idEnvoi 		NULL 		NULL 		NULL 			754315 	Using temporary; Using filesort
 
1 	SIMPLE 		envois 		eq_ref 	PRIMARY,date_lance 	PRIMARY 	4 		task.idEnvoi 		1 	Using where
1 	SIMPLE 		messages 	eq_ref 	PRIMARY 		PRIMARY 	4 		envois.idMessage 	1 	 
1 	SIMPLE 		sites 		eq_ref 	PRIMARY 		PRIMARY 	4 		messages.idSite 	1

INDEX sur la table task

Code:

1
2
3
4
 
Nom de l'index 	Type 		Cardinalité 		Champ
PRIMARY 		PRIMARY 	741565  	 	id
idEnvoi 		INDEX 	5  		 	idEnvoi

Je ne suis pas trés familier avec l'EXPLAIN mais,
ce que je ne comprend pas c'est que j'ai l'impression que la clé idEnvoi (index sur la table task) n'est pas utilisée, et que toute la table est balayée.

Est-ce que vous avez une idée ?
Merci pour votre aide.

19/01/2012, 08h32
Grandal

Bonjour,

Quels sont les types de données des différents IdEnvoi ??

As tu retiré les ORDER by pour faire explain sans eux ?
19/01/2012, 09h26
zakaa

Bonjour,

Les champs id sont en int(9).

J'ai déjà testé sans order by, le résultat est identique.

Sur la table task j'ai ajouté un index id,idEnvoi et idEnvoi,id.
Il me prend le idEnvoi,id en possible key mais ne l'utilise pas.

J'ai tenté le force index, pas de changement, il l'utilise pas.

Est-ce que vous pouvez me confirmer que je lis bien l'explain et qu'il balaye bien toute la table task, alors que l'utilisation d'un index devrait réduire cela ?
Au pire je m'attendais a ce qu'il lise 150.000 enregistrement (le nombre de task avec envois.id = task.idEnvoi ) et j'esperais que le limite reduirait encore cela ...

Comment est-ce que je peux m'assurer que les index sont bien construit ?

Un peu de bonnes pratiques...

1) Évite la guerre des étoiles ! :P

2) Les jointures s'écrivent depuis 1992 avec l'opérateur JOIN

3) Quand il y a plus d'une table dans une requête, celle-ci est plus facile à lire quand on utilise des alias.

4) Indenter correctement le code permet aussi de mieux comprendre la requête.

Voici votre requête récrite :
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 SELECT -- les colonnes nécessaires et pas étoile ! FROM envois e INNER JOIN task t ON e.id = t.idEnvoi INNER JOIN messages m ON e.idMessage = m.id INNER JOIN sites s ON m.idSite = s.id WHERE e.idEnvoyeur = 1 AND ( e.STATUS = 'ENV' OR ( e.STATUS = 'PRG' AND e.date_lance <= CURRENT_TIMESTAMP ) ) ORDER BY envois.priority DESC, envois.id ASC, task.id ASC LIMIT 0,150
Toutes les conditions de restriction (ce qu'il y a dans WHERE) portent sur la table "envois".
Y a t-il besoin dans le SELECT de colonnes provenant des autres tables ou certaines jointures sont-elles inutiles ?

Des index sur envois.idEnvoyeur, sur envois.STATUS et sur envois.date_lance seraient peut-être utiles pour ne pas balayer toute la table envois.

Il faut également que les colonnes messages.idSite, envois.idMessage et task.idEnvoi soient indexées puisqu'elles font fonction de clé étrangère, même si les tables sont peut-être en MyISAM et que les contraintes de clés étrangères ne sont pas utilisées.

19/01/2012, 12h22
zakaa

Merci pour ton aide CinePhil ;)

1) J'adore la guerre des étoiles ... le film, mais dans ce cas précis je ne récupère que les champs dont j'ai besoin.J'ai mis * pour vous alléger la lecture de la requête.
J'ai fait plusieurs tests en modifiant les champs du select, au pire en ne gardant que le task.id, cela ne change rien.

2) J'ai toujours écrit mes requêtes avec "=".Surement une habitude de 'vieux' Coboliste, ou la quasi totalité des programmes sont bien plus vieux que 1992 ..
J'ai gardé cette sale habitude par la suite, mais promis je vais faire un effort.

3-4)Mea culpa, c'est codé 'proprement', j'ai un peu modifier ça pour le mettre sur le forum pensant que ce serait plus clair .. erreur d'appreciation.

Les tables envois, messages et sites contiennent un centaine de ligne au maximum chacune, l’accès ne pose pas de problème à priori.
Seule date_lance dans la table envois est indexé.

Les tables sont bien en MyISAM, sans contraintes de clé étrangère.

Je vais essayer ta requête et voir ce que ça donne.
Encore merci pour ton aide.

Pas de modification sur l'explain avec la nouvelle requête.

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
SELECT
t.id as idTask, t.uniqueId as uniqueId, t.flag as flag,
e.fichier as fichier, e.id as idEnvoi,  e.priority as priority, e.status as status, 
s.id as idSite, s.txt as txtSite,
m.id as idMsg, m.txt as txtMsg
 
FROM envois e
INNER JOIN task t ON e.id = t.idEnvoi
INNER JOIN messages m ON e.idMessage = m.id
	INNER JOIN sites s ON m.idSite = s.id
WHERE e.idEnvoyeur = 1  
	AND 
	( 
		e.STATUS = 'ENV' 
		OR 
		(
			e.STATUS = 'PRG' 
			AND e.date_lance <= CURRENT_TIMESTAMP
		)
	)
ORDER BY e.priority DESC, e.id ASC, t.id ASC
LIMIT 0,150

19/01/2012, 14h17
selecta

Bonjour,

Peux tu faire un ANALYSE TABLE sur les tables en jeu et vérifier à nouveau si le plan d'exécution est plus favorable.

Combien d'enregistrements retourne cette requête sans le LIMIT ?

Cordialement

Selecta
19/01/2012, 14h19
CinePhil

Ma requête est identique à la tienne, seulement récrite différemment. Normal que tu ne constates pas de différence si tu n'as rien changé dans les index.

Au fait, elle s'exécute en combien de temps cette requête ?
19/01/2012, 15h55
zakaa

L'ANALYSE des tables a bien été fait, sans changement.
Je sais que seule la syntaxe diffère entre nos requêtes, mais j'ai déjà obtenu des gains significatifs dans d'autres cas juste en modifiant l'odre de la clause WHERE, donc je me suis dit pourquoi pas ;).

La table task contient entre 500.000 et 1.000.000 enregistrements.
La requête passe plusieurs fois dans un laps de temps donné, avec e.idEnvoyeur different.
Elle retourne entre 50.000 et 250.000 enregistrement sans le LIMITE.

Son temps d’exécution s’échelonne de 3 à 15s et dépend directement du nombre d'enregistrement qui serait retourné sans la clause LIMITE.

Autre chose que je trouve bizarre :
Le traitement lit 150 task, les traites, puis les DELETE une à une.
Le DELETE unitaire, via la clé primaire, sur la table task prend normalement 0.001s, mais certaine fois on a des pointes à 15s ! pour supprimer une ligne.Je ne sais pas si ça a un rapport avec le reste ...
QUICK et LOW_PRIORITY testé, sans changement.

Question subsidiaire :
Dans la pratique s.txt et m.txt sont identiques pour les 150 lignes retournées.
Il s'agit de VARCHAR(1500).
Ça demande du dev, mais est-ce que vous pensez que je gagnerais à retirer les tables messages et sites de cette requête, et d'en faire une autre juste pour récupérer ces champs ?
19/01/2012, 16h09
CinePhil

Tout ça ne me dit toujours pas si les tables sont correctement indexées !
Tu peux fournir la description complète des tables impliquées dans la requête (résultat e SHOW CREATE TABLE la_table) et l'explain complet de la requête ?

Citation:

Autre chose que je trouve bizarre :
Le traitement lit 150 task, les traites, puis les DELETE une à une.

Ce n'est pas une requête DELETE, tu dois donc parler d'autre chose. Quel traitement ?

En tout cas, si tu as une opération qui supprime des lignes sur la table task, ça bloque la table task. Les autres requêtes, dont ta requête SELECT ne peuvent pas s'exécuter tant que la table est bloquée.

Si tu expliques mieux ce que tu cherches à faire, on pourra t'aider plus efficacement. Lis la phrase en bleu de ma signature et applique son principe !
19/01/2012, 17h43
zakaa
Pour les indexs toutes les tables ont PRIMARY KEY (`id`).

La table task à en plus KEY `idEnvoi` (`idEnvoi`)
Et j'ai ajouté un index sur id+idEnvoi et idEnvoi+id pour faire des tests.

Je n'ai plus accès aux tables jusqu’à demain, je vous fournirais le resultat du SHOW.

Pour l'explain complet de la requête il est sur le premier post.

Pour la description du traitement, je pensais être assez clair :

Citation:

Le traitement lit 150 task, les traites, puis les DELETE une à une.
Le DELETE unitaire, via la clé primaire, sur la table task prend normalement 0.001s, mais certaine fois on a des pointes à 15s ! pour supprimer une ligne.Je ne sais pas si ça a un rapport avec le reste ...
QUICK et LOW_PRIORITY testé, sans changement.

Donc la requete est lancée, les lignes récupérées sont lues une a une (fetch).
La task est traitée, puis supprimée unitairement, via la clé primaire :
DELETE FROM task WHERE id = $id
On lit ensuite la ligne suivante.

C'est le même traitement qui fait le select et le delete, séquentiellement.
Aucun autre traitement n’accède à cette table.
Je ne pense pas qu'il puisse y avoir de blocage dans ce cas ?

Le temps d’exécution de la requête est mesuré directement sur le traitement, en php.Le temps est pris juste avec la requête et juste après, pas d'autres traitement effectués.

EDIT:
Petite imprécision sur le delete, la mesure est prise avant et aprés l'appel à une fonction de ce type :
Code:

1 2 3 4 5 function smart_mysql_query($sql){ $res = mysql_query($sql) or die(" TEXTE "); if(!$res) return false; return $res; }
26/01/2012, 09h46
zakaa
J'ai résolu mon soucis en modifiant l'ORDER BY :
Code:

1 2 3 ORDER BY envois.priority DESC, envois.id ASC, task.id ASC devient ORDER BY task.idEnvoi ASC, task.id ASC
La requête utilise alors bien l'index idEnvoi + id de la table task, et s’exécute en moins d'1s.

Il n'y a plus de problèmes concernant le temps d’exécution des DELETE, ça par contre je ne me l'explique pas.
Je ferais des tests un peu plus tard pour comprendre (enfin, je le mets sur ma liste des tests à faire un peu plus tard ... ).

Merci pour le temps passé sur mon problème.