Discussion :

[moukat]

Bonjour

Voici une des requêtes que je n'arrive pas à optimiser. (EXPLAIN m'indique toujours en extra using filesort).

Mes tables :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
 
TABLE : t_bloc // table des articles
id_bloc 
article
date_modif
id_membre_bloc // indique l'id du membre qui a posté l'article, 0 si il s'agit d'un article posté par moi même
...
(dans mon test il y a 170 000 enregistrements dans cette table)
 
TABLE : t_taxon_bloc //table qui relie les taxons (rubriques, tags.... et les articles) (table de relation)
id_taxon
id_bloc
(il y a 215 000 enregistrements dans cette table)
 
TABLE : t_taxon 
id_taxon
taxon (<-- nom de la rubrique/tag etc.)
parent_id (id du taxon parent : exemple si une rubrique appartient à une catégorie qui elle même est un taxon)

La requête

Code :

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

1
2
3
4
5
6
7
8
9
 
SELECT B.id_bloc, B.date_modif FROM t_bloc AS B
    INNER JOIN t_taxon_bloc AS TB
    ON (TB.id_bloc=B.id_bloc)
    INNER JOIN t_taxon T
    ON (T.id_taxon=TB.id_taxon)
    WHERE T.parent_id=66 
    ORDER BY B.date_modif DESC
    limit 10

J'ai 166 000 articles qui répondent aux critères du where. je demande à en récupérer que 10 sachant que date_modif est un index , idem pour parent_id, id_taxon et id_bloc

Dans t_taxon_bloc la clé primaire c'est (id_taxon, id_bloc), dans t_bloc c'est id_bloc, dans t_taxon c'est id_taxon (voir les tables plus bas)

Il met 2,65s pour me retourner les 10 demandés ou seulement 0,0006s sans le order by

Le explain m'indique ceci :

Code :

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

1
2
3
4
    id    select_type    table    type    possible_keys    key    key_len    ref    rows    Extra
1 	SIMPLE 	T 	ref 	PRIMARY,parent_id 	parent_id 	4 	const 	7 	Using temporary; Using filesort
1 	SIMPLE 	TB 	ref 	PRIMARY 	PRIMARY 	4 	T.id_taxon 	2150 	Using index
1 	SIMPLE 	B 	eq_ref 	PRIMARY 	PRIMARY 	4 	TB.id_bloc 	  1

On voit bien cette ligne pour la table T : Using temporary; Using filesort ce qui est très mauvais en terme de perf à mon avis, mais impossible de m'en débarrasser.
on voit également que B n'utilise pas l'index date_modif

rows pour les 3 tables ne me semble pas énorme et ne semble pas justifier ces 2,65s de temps d'execution :/

Petite précision mes table utilisent le moteur Myisam

[moukat]

Infos complémentaires

J'ai essayé cette requete en faisant un order non pas sur date_modif mais id_bloc :

Code :

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

1
2
3
4
5
6
7
8
9
 
 SELECT B.id_bloc  FROM t_bloc AS B
    INNER JOIN t_taxon_bloc AS TB
    ON (TB.id_bloc=B.id_bloc)
INNER JOIN t_taxon T
ON (T.id_taxon=TB.id_taxon)
    WHERE T.parent_id=66
    ORDER BY B.id_bloc DESC
    LIMIT 10

résultat j’obtiens 0.45s soit 6 fois plus rapide (même si j'ai toujours un USING filesort), mais dès que j'ajoute date_modif comme ceci je repasse à 2,6 s :

Code :

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

1
2
3
4
5
6
7
8
 SELECT B.id_bloc, B.date_modif  FROM t_bloc AS B
    INNER JOIN t_taxon_bloc AS TB
    ON (TB.id_bloc=B.id_bloc)
INNER JOIN t_taxon T
ON (T.id_taxon=TB.id_taxon)
    WHERE T.parent_id=66
    ORDER BY B.id_bloc DESC
    LIMIT 10

je ne comprend pas le problème avec ce date_modif, c'est un bête datetime qui en plus est en index
j'ai évidemment essayé de le retiré des index, de le mettre dans la clause where etc. mais rien n'y fait dès que j'ajoute date_modif dans ma requête que ça soit dans le champ à récupérer ou dans le order by, mysql s'affole

d'ailleur mysql s'affole même si j'ajoute un champ autre que date_modif dans la liste des champs à afficher, dans le order By ou la clause where

[punkoff]

bonjour,

que donne la requete en mettant cet index : create index idx on t_bloc (id_bloc, date_modif desc)

[moukat]

Bonjour

Tout d'abord merci d'avoir pris le temps de me répondre.

Toujours le même problème même en ajoutant le nouvel Index :

create index idx on t_bloc (id_bloc, date_modif desc)

Je remets ici un peu plus de détails concernant mes tables et mon problème, et je prends comme exemple une requête encore plus simple mais qui pose exactement le même problème :

J'ai également réduit le nombre d'enregistrements pour m'approcher le plus possible de la réalité.

Voici un aperçu de mes tables concernées

Code :

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

1
2
3
4
5
6
7
t_bloc
id_bloc
titre
contenu
id_membre_bloc
date_modif
etc.

Clé primaire sur id_bloc
Index sur id_membre_bloc
Index sur date_modif
et maintenant index sur (id,bloc,date_modif)
J'ai 17 000 enregistrements dans cette table

Code :

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

1
2
3
t_taxon_bloc
id_taxon
id_bloc

Clé primaire sur (id_taxon, id_bloc)
J'ai 67 000 enregistrements dans cette table (logique vu qu'un article peut être associé à plusieurs taxons (rubrique, tags...)

Code :

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

1
2
3
4
5
t_taxon
id_taxon
titre
taxonomie
parent_id

Clé primaire sur id_taxon
Index sur parent_id
J'ai 29 enregistrements dans cette table (il y a assez peu de rubriques tags etc.)

La table t_taxon liste tous les taxons du site (taxonomie="rubrique" ou "tag" ou "categorie")
parent_id détermine si un taxon est dépendant d'un autre taxon (exemple une rubrique peut être dépendante d'une catégorie qui elle même est un taxon).

La table t_bloc correspond aux articles (id_membre_bloc correspond à l'id du membre ayant rédigé éventuellement l'article sinon c'est à 0)

La table t_taxon_bloc est la table de relation entre l'article et les taxons.
Par conséquent un article est associé au moins à une rubrique et potentiellement à un ou plusieurs tags.
On a une relation 1-n

Voici donc une requête encore plus simple qui pose exactement le même problème

Code :

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

1
2
3
4
5
SELECT B.id_bloc FROM t_bloc AS B 
INNER JOIN t_taxon_bloc AS TB ON B.id_bloc=TB.id_bloc 
WHERE TB.id_taxon=12
ORDER BY B.date_modif
LIMIT 0,10

12 correspond à la rubrique 12. Par conséquent je souhaite afficher 10 articles classés par date de modification de la rubrique n°12

A première vue requête très simple qui ne devrait pas bouleverser mysql et pourtant lorsque je fais un EXPLAIN de cette requête j'obtiens ceci (même avec la clé que tu m'a demandé d'ajouter)

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 	TB 	ref 	PRIMARY,id_bloc 	PRIMARY 	4 	const 	5256 	Using index; Using temporary; Using filesort
1 	SIMPLE 	B 	eq_ref 	PRIMARY,idx 	PRIMARY 	4 	TB.id_bloc 	1

On constate les horribles Using temporary; Using filesort.
Rows devrait également être à 10 et non à 5226 étant donné le limit 0,10.

Autant dire que malgré la simplicité de la requête celle-ci est très gourmande. Dans ma rubrique 12 j'ai 5256 articles et cette requête met environ 0,1s ce qui n’est pas acceptable pour si peu d'articles et une requête aussi simple.

D'ailleurs j'obtiens le même temps d'exécution et le même explain avec cette requête

Code :

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

1
2
3
4
SELECT count(*) FROM t_bloc AS B 
INNER JOIN t_taxon_bloc AS TB ON B.id_bloc=TB.id_bloc 
WHERE TB.id_taxon=12
ORDER BY B.date_modif

je n'arrive vraiment pas à cerner l'optimiseur

J'ai également essayé d'intégrer date_modif dans le where et/ou dans les champs à afficher mais rien n'a faire.

Si je reprends la requête du 1er post en prenant en compte le fait que j'ai réduit le nombre de mes enregistrements :

Code :

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

1
2
3
4
5
6
7
8
9
 
SELECT b.id_bloc, B.date_modif FROM t_bloc AS B
INNER JOIN t_taxon_bloc AS TB
ON TB.id_bloc=B.id_bloc
INNER JOIN t_taxon T
ON T.id_taxon=TB.id_taxon
WHERE T.parent_id=66
ORDER BY B.date_modif DESC
LIMIT 0,10

je suis à 0,22s (énorme).

L'explain :

Code :

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

1
2
3
1 	SIMPLE 	T 	ref 	PRIMARY,parent_id 	parent_id 	4 	const 	12 	Using temporary; Using filesort
1 	SIMPLE 	TB 	ref 	PRIMARY,id_bloc 	PRIMARY 	4 	T.id_taxon 	31 	Using index
1 	SIMPLE 	B 	eq_ref 	PRIMARY,idx 	PRIMARY 	4 	TB.id_bloc 	1

Si je fais un ORDER BY B.id_bloc à la place de ORDER BY B.date_modif (ce qui ne sert à rien pour moi mais permet peut-être d'identifier le problème) j'obtiens également 0.22s mais par contre si en plus je retire B.date_modif des champs à afficher je passe à 0,03s

Ça fait une semaine que je suis dessus et ça me bloque réellement pour avancer dans mon projet
Ces requêtes me rendent chèvre

[punkoff]

En fait, les 5225 viennent du fait qu'il trie entièrement les données avant de n'en sélectionner que 10 je penses.

une autre approche :

Code :

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

1
2
3
4
5
6
7
8
9
 
SELECT B.id_bloc, B.date_modif  
FROM t_bloc AS B
where exists (
 select 1 from t_taxon_bloc AS TB 
 INNER JOIN t_taxon T ON T.id_taxon=TB.id_taxon
 WHERE T.parent_id=66 and ON TB.id_bloc=B.id_bloc)
ORDER BY B.id_bloc DESC
LIMIT 10

Concernant l'indexation essayez avec celui indiqué et aussi son inverse : date / id

[moukat]

en fait dans la requête du 1er post je me suis trompé c'est order by B.date_modif pas order by B.id_bloc (erreur de copié collé).

Alors j'ai testé votre requête, celle ci semble très efficace et j'obtiens une nette amélioration à condition de faire l'index suivant comme vous me l'avez indiqué :

create index idx on t_bloc (date_modif desc, id_bloc)

Donc si je fais

Code :

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

1
2
3
4
5
6
7
8
SELECT B.id_bloc, B.date_modif  
FROM t_bloc AS B
WHERE EXISTS (
 SELECT 1 FROM t_taxon_bloc AS TB 
 INNER JOIN t_taxon T ON T.id_taxon=TB.id_taxon
 WHERE T.parent_id=66 AND TB.id_bloc=B.id_bloc)
ORDER BY B.date_modif DESC
LIMIT 0,10

la requête s’exécute en 0.0015s autant dire une net amélioration

et voici le explain :

Code :

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

1
2
3
4
5
 
id 	select_type 	table 	type 	possible_keys 	key 	key_len 	ref 	rows 	Extra
1 	PRIMARY 	B 	index 	NULL 	idx2 	12 	NULL 	10 	Using where; Using index
2 	DEPENDENT SUBQUERY 	TB 	ref 	PRIMARY,id_bloc 	id_bloc 	4 	B.id_bloc 	1 	 
2 	DEPENDENT SUBQUERY 	T 	eq_ref 	PRIMARY,parent_id 	PRIMARY 	4 	TB.id_taxon 	1 	Using where

Ce qui semble bien plus optimisé. va falloir que je me penche dessus pour comprendre cette façon de faire

Par contre :

Avec un limit 1000,10 (si je veux afficher la page 100) je suis à 0,15s mais l'explain ne saute pas (pas de filesort ou temporary)
mais est-ce normal d'être quand même à 0.15s si on va à la page 100? (soit un LIMIT 1000,10 ) Si tout est bien trié et les index bien utilisés peu importe la page on devrait avoir quasiment le même temps d’exécution non?
Pas certain que ca soit normal d'atteindre encore 0.15s en affichant la 100ème page :/

Autre test, j'ai essayé sans l'index (date_modif,id_bloc) mais uniquement avec date_modif, la requête reste efficace mais jusqu'à un certain point. Si je fais LIMIT 1000,10, l'EXPLAIN saute et la requête met 1,15s

De plus si je reprends la requête pour faire un count c'est le désastre 1.3s :

Code :

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

1
2
3
4
5
6
7
 
SELECT count(*)
FROM t_bloc AS B
WHERE EXISTS (
 SELECT 1 FROM t_taxon_bloc AS TB 
 INNER JOIN t_taxon T ON T.id_taxon=TB.id_taxon
 WHERE T.parent_id=66 AND TB.id_bloc=B.id_bloc)

[punkoff]

Beuh oui c'est normal de tel temps d'execution.

La syntaxe utilisée force le sgbd à partir de l'index pour faire son travail.

C'était le but de mes questions avec l'indexation.


Sinon regardez votre parametre inodb buffer pool size, il n'est peut être pas assez grand

[moukat]

ok je dois avouer que mes compétences atteignent vite leur limites (je suis resté des années sans développer)

Concernant le innodb_log_buffer_size, je suis allez dans my.ini (je sais pas si c'est le bon endroit pour avoir l'info demandé)

Et j'ai vu la ligne suivante :

#innodb_log_buffer_size = 8M

petite précision j'utilise le Moteur Myisam

Sinon j'ai ces lignes dans my.ini :

[myisamchk]
key_buffer = 20M
sort_buffer_size = 20M
read_buffer = 2M
write_buffer = 2M

[moukat]

N'y a t-il pas un moyen avec de simple jointure d'obtenir un résultat correct sans passer par des requêtes imbriquées?
Lorsque je fais des recherches les requêtes imbriquées semblent quand même en général à déconseiller par rapport aux jointures.
Surtout que j'arrive pas a comprendre pourquoi des jointures aussi simples ne sont pas optimisés et que l'optimiseur n'utilise pas correctement les index.

J'ai un peu peur qu'en production, les requêtes imbriquées fassent l'effet inverse.

je vais essayer d'introduire un panel d'enregistrements le plus représentatif possible et refaire des essais, mais je reste encore ouvert à d'autres solutions possibles en utilisant si possibles les jointures (ce qui me semble normalement le plus approprié dans ce cas de figure ) sans utiliser des FORCE INDEX ou des STRAIGHT_JOIN

De mon côté je vous tient au courant si j'ai trouvé une solution pour palier à cette utilisation de requêtes imbriquées ou si mon panel d'enregistrements tests à changer la donner au niveau de l'optimiseur

PS : EXISTS est équivalent à IN?
PS2: en tout cas merci pour le temps passer à m'aider

[SQLpro]

N'y a t-il pas un moyen avec de simple jointure d'obtenir un résultat correct sans passer par des requêtes imbriquées?

oui :

Code :

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

1
2
3
4
5
6
7
8
9
SELECT DISTINCT B.id_bloc, B.date_modif  
FROM   t_bloc AS B
       INNER JOIN t_taxon_bloc AS TB 
             ON TB.id_bloc=B.id_bloc
       INNER JOIN t_taxon T 
             ON T.id_taxon=TB.id_taxon
WHERE  T.parent_id=66 
ORDER  BY B.date_modif DESC
LIMIT  0,10

Lorsque je fais des recherches les requêtes imbriquées semblent quand même en général à déconseiller par rapport aux jointures.

C'est exact avec MySQL qui dispose d'un des plus mauvais optimiseur... Ce n'est bien entendu pas le cas général !

Surtout que j'arrive pas a comprendre pourquoi des jointures aussi simples ne sont pas optimisés et que l'optimiseur n'utilise pas correctement les index.

Bis repetita....

J'ai un peu peur qu'en production, les requêtes imbriquées fassent l'effet inverse.

je vais essayer d'introduire un panel d'enregistrements le plus représentatif possible et refaire des essais, mais je reste encore ouvert à d'autres solutions possibles en utilisant si possibles les jointures (ce qui me semble normalement le plus approprié dans ce cas de figure ) sans utiliser des FORCE INDEX ou des STRAIGHT_JOIN

ça risque d'être pire à terme du fait des changements possible de distribution des données

De mon côté je vous tient au courant si j'ai trouvé une solution pour palier à cette utilisation de requêtes imbriquées ou si mon panel d'enregistrements tests à changer la donner au niveau de l'optimiseur

PS : EXISTS est équivalent à IN?

Absolument pas d'un point de vu mathématique.

PS2: en tout cas merci pour le temps passer à m'aider

A +

[moukat]

SQLPro, la requête que tu me donnes correspond à celle que j'avais au départ (voir mon 1er post)
Hormis le DISTINCT qui a été ajouté.

Le soucis c'est qu'elle n’est toujours pas bien optimisée, si je fais un EXPLAIN, j'ai ceci :

Code :

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

1
2
3
4
id 	select_type 	table 	type 	possible_keys 	key 	key_len 	ref 	rows 	Extra
1 	SIMPLE 	T 	ref 	PRIMARY,parent_id 	parent_id 	4 	const 	12 	Using temporary; Using filesort
1 	SIMPLE 	TB 	ref 	PRIMARY,id_bloc 	PRIMARY 	4 	T.id_taxon 	31 	Using index
1 	SIMPLE 	B 	eq_ref 	PRIMARY,idx_idbloc_date 	PRIMARY 	4 	TB.id_bloc 	1

encore et toujours ces horribles using temporary et filesort. Et ma requête est beaucoup trop longue à s’exécuter par rapport au nombre d'enregistrements.
Dans le test effectué cette fois-ci (j'ai 2 000 enregistrements dans t_blocs, 2 000 dans t_taxon_bloc, et 29 dans t_taxon)
cette requête doit me retourner les 10 premiers de 407 enregistrements (autrement dit quasiment rien pour une BDD)
et pourtant elle a mis 0,02s. Si j'ajoute quelques centaines d'enregistrements, le temps d’exécution grimpe rapidement.

En production, lorsque j'aurais des dizaines de milliers d'enregistrements, je vais frôler la seconde.

La solution de punkoff est bien plus rapide (0.0031s), mais ces fichues requêtes imbriquées et le EXISTS m'embêtent un peu :/

Il y a forcement un truc à faire avec ces jointures mais je ne trouve pas quoi :/ j'ai tourné, viré mes index dans tous les sens mais il n'y a rien à faire.

D'ailleurs si je prends une requête encore plus simple avec une seule jointure :

Code :

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

1
2
3
4
5
6
SELECT DISTINCT B.id_bloc, B.date_modif  
FROM t_bloc AS B
INNER JOIN t_taxon_bloc AS TB ON TB.id_bloc=B.id_bloc
WHERE TB.id_taxon=44
ORDER BY B.date_modif DESC
LIMIT 0,10

En gros ici je fais afficher les 10 premiers articles de la rubrique 44, on constate toujours une mauvaise optimisation :

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 	TB 	ref 	PRIMARY,id_bloc 	PRIMARY 	4 	const 	292 	Using index; Using temporary; Using filesort
1 	SIMPLE 	B 	eq_ref 	PRIMARY,idx_idbloc_date 	PRIMARY 	4 	TB.id_bloc 	1

Par contre, dès que je retire le ORDER BY, c'est parfait :

Code :

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

1
2
3
4
5
6
SELECT B.id_bloc
FROM t_bloc AS B 
INNER JOIN t_taxon_bloc AS TB 
ON TB.id_bloc=B.id_bloc
WHERE TB.id_taxon=44 
LIMIT 0,10

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 	TB 	ref 	PRIMARY,id_bloc 	PRIMARY 	4 	const 	292 	Using index
1 	SIMPLE 	B 	eq_ref 	PRIMARY,idx_idbloc_date 	PRIMARY 	4 	TB.id_bloc 	1 	Using index

La requête met 0.0004s.

Il n'est donc pas normal qu'avec un simple ORDER BY d'un champ ajouté en index et pour si peu d'enregistrementd on passe a 0,0055s et les temporary et filesort.

[moukat]

Voici une solution qui m'a été proposée, qu'en pensez vous?

Code :

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

1
2
3
4
5
6
SELECT B.date_modif FROM t_bloc AS B FORCE INDEX (idx_date_idbloc) 
INNER JOIN t_taxon_bloc AS TB 
ON (TB.id_bloc=B.id_bloc)
WHERE TB.id_taxon=44 
order by B.date_modif DESC
limit 0,10

En termes de performances, ça semble équivalent aux requêtes imbriquées avec EXISTS, et même si je suis pas fan non plus du force index, je n'arrive pas vraiment à me décider. Je n'ai pas le niveau de compétences pour déterminer ce qui est mieux et le moins risqué en prod.

Voici le explain associé :

Code :

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

1
2
3
4
 
id 	select_type 	table 	type 	possible_keys 	key 	key_len 	ref 	rows 	Extra
1 	SIMPLE 	B 	index 	NULL 	idx_date_idbloc 	12 	NULL 	10 	Using index
1 	SIMPLE 	TB 	eq_ref 	PRIMARY,id_bloc 	PRIMARY 	8 	const,B.id_bloc 	1 	Using index