1

Hello,

j'ai un problème de performance avec une requête MySQL que je n'arrive pas à comprendre (pour info, c'est la cause des lenteurs actuelles de la page "mes sujets"). J'ai essayé de le reproduire avec les conditions les plus simples possibles, voilà ce que ça donne :SELECT member.id, member_cache.id, section.id, topic.id FROM topic INNER JOIN ( section INNER JOIN ( member LEFT JOIN (member_cache) ON member_cache.id = member.id ) ON member.id = section.last_member ) ON section.id = topic.section WHERE topic.last_time IS NOT NULL ORDER BY topic.last_time DESC LIMIT 0, 1
Schéma des tables utilisées dans cette requête
CREATE TABLE `member` ( `id` int(10) unsigned NOT NULL ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE `member_cache` ( `id` int(10) unsigned NOT NULL ) ENGINE=MyISAM DEFAULT CHARSET=utf8; CREATE TABLE `section` ( `id` int(10) unsigned NOT NULL, `last_member` int(10) unsigned NOT NULL DEFAULT '0' ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE `topic` ( `id` int(10) unsigned NOT NULL, `section` int(10) unsigned NOT NULL, `last_time` int(10) unsigned NOT NULL ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; ALTER TABLE `member` ADD PRIMARY KEY (`id`); ALTER TABLE `member_cache` ADD PRIMARY KEY (`id`); ALTER TABLE `section` ADD PRIMARY KEY (`id`); ALTER TABLE `topic` ADD PRIMARY KEY (`id`), ADD KEY `section__last_time` (`section`,`last_time`), ADD KEY `last_time` (`last_time`);

C'est une requête qui est supposée sortir les N derniers sujets (en l'occurrence un seul) triés par date de dernière activité (table "topic"), ainsi que leur section parente (table "section"), le dernier posteur de chaque sujet (table "member") et quelques informations supplémentaires sur ce posteur (table "member_cache"). Si j'exécute cette requête avec un "EXPLAIN", j'obtiens ça :
idselect_typetabletypepossible_keyskeykey_lenrefrowsExtra
1SIMPLEsectionALLPRIMARYNULLNULLNULL2188Using temporary; Using filesort
1SIMPLEmembereq_refPRIMARYPRIMARY4temporary.section.last_member1Using index
1SIMPLEmember_cacheeq_refPRIMARYPRIMARY4temporary.section.last_member1Using index
1SIMPLEtopicrefsection__last_time,last_timesection__last_time4temporary.section.id106Using index condition

Ce que je ne comprends pas c'est que MySQL se retrouve à scanner 2188 lignes de la table "section" (1ière ligne du tableau, valeurs en rouge) alors que la seule utilisation de cette table est faite pour récupérer la section parente de chaque sujet, et qu'on utilise pour ça son champ "id" qui se trouve être sa clé primaire. C'est d'ailleurs bien indiqué dans la première ligne, on voit comme "possible_keys" PRIMARY, mais au final aucune clé n'est utilisée pour une raison qui m'échappe et on se retrouve à scanner toute la table.

Pourquoi est-ce que MySQL décide de ne pas utiliser cet index ? C'est d'autant plus curieux que si je supprime la jointure la plus intérieure sur "member_cache" (qui a priori n'influe en rien sur la table "section"), alors l'index est correctement utilisé et la requête tourne beaucoup plus vite. Ça parle à quelqu'un ? À défaut, vous savez comment je pourrais avoir plus d'informations sur les raisons qui provoquent ce choix de MySQL ?

Si vous voulez reproduire en local, j'ai exporté la base et les données utilisées ici, et j'ai constaté le problème sur les versions 5.5.35-log et 5.6.15 de MySQL.
avatar
All right. Keep doing whatever it is you think you're doing.
------------------------------------------
Besoin d'aide sur le site ? Essayez par ici :)

2

C'est pas le MyISAM le premier probleme ?
avatar
Proud to be CAKE©®™


GCC4TI importe qui a problème en Autriche, pour l'UE plus et une encore de correspours nucléaire, ce n'est pas ytre d'instérier. L'état très même contraire, toujours reconstruire un pouvoir une choyer d'aucrée de compris le plus mite de genre, ce n'est pas moins)
Stalin est l'élection de la langie.

3

Alors je préfèrerais avoir une explication rationnelle à ce problème, ensuite tu pourras lancer un troll MyISAM vs InnoDB si tu veux, mais pour le moment c'est plutôt des pistes qui m'intéressent.
avatar
All right. Keep doing whatever it is you think you're doing.
------------------------------------------
Besoin d'aide sur le site ? Essayez par ici :)

4

De mémoire MySQL a du mal avec les index en MyISAM, et la avec ta triple jointure tu fait marcher les indexes a fond.
Je ne suis pas expert MySQL c'est certain, et je dit ça de mémoire, et surtout je ne cherche pas a lancer un troll.

Je laisse les experts es MySQL trouver une vrai explication, mais mon premier réflexe serait de tester une base équivalente avec l'autre moteur pour voir si ca change quelque chose.

Surement pas du tout la meme chose mais : https://www.percona.com/blog/2006/05/29/join-performance-of-myisam-and-innodb/
avatar
Proud to be CAKE©®™


GCC4TI importe qui a problème en Autriche, pour l'UE plus et une encore de correspours nucléaire, ce n'est pas ytre d'instérier. L'état très même contraire, toujours reconstruire un pouvoir une choyer d'aucrée de compris le plus mite de genre, ce n'est pas moins)
Stalin est l'élection de la langie.

5

En l'occurrence le résultat est exactement le même avec InnoDB, je viens de tester sur l'exemple du post ./1.

Par contre il y a un début de piste dans le lien que tu as posté sur IRC : MySQL semble essayer d'inverser une jointure et se retrouve à faire quelque chose de très inefficace.

Solution n°1 : si je remplace le premier INNER JOIN (celui sur "section") par un LEFT JOIN, la requête ne scanne plus qu'une seule ligne et redevient instantanée. Plus qu'à comprendre d'où vient ce mauvais choix, et essayer de l'annuler sans utiliser de LEFT JOIN.

[edit]

Solution n°2, mais vraiment très MySQL-specific donc je n'aime pas trop non plus : forcer l'utilisation de l'index sur la table "topic" (i.e. "... FROM topic FORCE INDEX (PRIMARY) INNER JOIN ...")

Solution n°3 : supprimer l'index "section__last_time" sur la table "topic", c'est lui qui induit MySQL en erreur visiblement... sauf que j'en ai quand même besoin pour trier les sujets dans une même section.

Solution n°4 : au lieu de le supprimer, remplacer cet index en intercalant une colonne supplémentaire (dont je ne sers de toutes façons) ; ça corrige le problème sur le MySQL d'Infomaniak mais pas chez moi, donc c'est vraiment très fragile comme fix. J'imagine qu'il va réapparaître tout seul un de ces jours...
avatar
All right. Keep doing whatever it is you think you're doing.
------------------------------------------
Besoin d'aide sur le site ? Essayez par ici :)

6

Ok
avatar
Proud to be CAKE©®™


GCC4TI importe qui a problème en Autriche, pour l'UE plus et une encore de correspours nucléaire, ce n'est pas ytre d'instérier. L'état très même contraire, toujours reconstruire un pouvoir une choyer d'aucrée de compris le plus mite de genre, ce n'est pas moins)
Stalin est l'élection de la langie.

7

Bon, comme prévu avec la solution n°4 le problème est réapparu au premier changement que j'ai fait sur la table "section". MySQL recommence à vouloir scanner cette table en premier, et le problème disparaît si je supprime l'index de "topic" sur les colonnes "section" et "last_time" mais j'en ai besoin donc pas possible.

Je ne sais pas trop comment creuser sur les raisons qui lui font choisir ce plan d'exécution, il est tellement mauvais par rapport à l'optimal que je dois forcément m'être planté quelque part :/
avatar
All right. Keep doing whatever it is you think you're doing.
------------------------------------------
Besoin d'aide sur le site ? Essayez par ici :)

8

Tu peux pas découper ta requêtes en plusieurs bouts "indépendants" ? (j'avoue ne pas savoir ce que ça peux impliquer au niveau perfs, mais ça ne peux pas être pire que ce qu'il fait actuellement non?)
avatar
Proud to be CAKE©®™


GCC4TI importe qui a problème en Autriche, pour l'UE plus et une encore de correspours nucléaire, ce n'est pas ytre d'instérier. L'état très même contraire, toujours reconstruire un pouvoir une choyer d'aucrée de compris le plus mite de genre, ce n'est pas moins)
Stalin est l'élection de la langie.

9

Je ne suis pas tellement libre sur cette requête, elle est générée donc la marge de manœuvre est limitée. Par exemple, je suis obligé de n'avoir qu'un seul select avec des jointures.
avatar
All right. Keep doing whatever it is you think you're doing.
------------------------------------------
Besoin d'aide sur le site ? Essayez par ici :)

10

Ok :/
Je vais/suis pas etre d'une aide formidable desole sad
avatar
Proud to be CAKE©®™


GCC4TI importe qui a problème en Autriche, pour l'UE plus et une encore de correspours nucléaire, ce n'est pas ytre d'instérier. L'état très même contraire, toujours reconstruire un pouvoir une choyer d'aucrée de compris le plus mite de genre, ce n'est pas moins)
Stalin est l'élection de la langie.

11

Merci quand même smile

Du coup je tente sur StackOverflow : http://stackoverflow.com/questions/30873912/inefficient-execution-plan-on-mysql-query-with-multiple-joins

[edit] Bon, quelqu'un a répondu et ça résout effectivement le cas que j'avais posté donc j'ai du accepter sa solution, mais :

1 - Je ne comprends pas pourquoi ça règle le problème
2 - Je n'arrive pas à l'adapter au cas réel sad
avatar
All right. Keep doing whatever it is you think you're doing.
------------------------------------------
Besoin d'aide sur le site ? Essayez par ici :)

12

Pour info, ce matin ça lag encore, alors qu'hier ça allait normalement, je crois. Tu le sais peut-etre déjà, je sais pas si t'as changé qqchose, et si c'est pour tout le monde pareil. C'est pour info. smile

13

Je viens de remarquer aussi, et je n'ai rien changé :/ (enfin si j'ai fait un changement hier mais ça fonctionnait impeccablement bien juste après)
avatar
All right. Keep doing whatever it is you think you're doing.
------------------------------------------
Besoin d'aide sur le site ? Essayez par ici :)

14

(désolé, je suis une quiche en SQL B (avancé), donc j'ai pas vraiment de solution a cette question complexe, mais je me disais qu'en ajoutant un peu d'huile dans les jointures, ca passerait ptet mieux? oui je sais, loin, toussa, désolé sad....)