Performance catastrophique requête imbriquée [Résolu]

[Galdon]

Bonjour,

J'ai une requête composée qui prend anormalement beaucoup de temps :

Code :

1
2
3
4
5
6
7
8
9
 
SELECT user_id, username, user_type, user_posts
FROM phpbb_users
WHERE user_id IN(
	SELECT DISTINCT topic_poster AS user_id
	FROM phpbb_topics
	WHERE forum_id = 33
)
-- Affichage des enregistrements 0 - 29 (57 total, Traitement en 23.9452 sec.)

Alors j'ai testé les deux requêtes séparément, et là elles s'exécutent normalement (très rapide) :

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
 
/*
 * Requête principale
 */
SELECT user_id, username, user_type, user_posts
FROM phpbb_users
WHERE user_id IN(
	1622, 1668, 1691, 1701, 1720, 1696, 1767, 1763, 1822, 1855, 1889, 1874,
	1908, 1916, 1920, 1925, 1909, 1955, 1956, 1975, 1991, 2576, 2671, 2667,
	2668, 2713, 2724, 2726, 2738, 2740, 2750, 2751, 2755, 2758, 2759, 2753,
	2771, 2772, 2773, 2775, 2782, 2813, 2848, 2851, 2868, 2879, 2899, 2814,
	2910, 2922, 2909, 2897, 2709, 2893, 2933, 2754, 2923
)
-- Affichage des enregistrements 0 - 29 (57 total, Traitement en 0.0008 sec.)
 
 
/*
 * Sous-requête
 */
SELECT DISTINCT topic_poster AS user_id
FROM phpbb_topics
WHERE forum_id = 33
-- Affichage des enregistrements 0 - 29 (57 total, Traitement en 0.0015 sec.)

Ensuite j'ai lancé un profilage de ma requête, et ça crache de partout, je vois plein de "Copying to tmp table", vous pouvez voir tout le profilage ici.

Comment faire pour corriger ce problème ?

Merci

[SQLpro]

Malheureusement MySQL n'a jamais été conçu pour avoir de bonnes performances sur les requêtes, contrairement à une idée reçue...
Voici ce que je Pense de MySQL : http://blog.developpez.com/sqlpro/p9...udre-aux-yeux/
Et voici un Benchmark : http://blog.developpez.com/sqlpro/p9...lles-en-sql-1/
Bref, si vous voulez réellement des performances, prenez un vrai SGBD relationnel et non ersatz.... Regardez par exemple du côté de PostGreSQL ou mieux SQL Server (version gratuite avec Express 2008 R2)...

Dans l'immédiate, avent de passer à un autre SGBDR plus performant, vérifiez cependant que vous avez bien un index sur :

Code :

phpbb_topics (forum_id , topic_poster)

et sur

Code :

phpbb_users (user_id, username, user_type, user_posts)

Si c'est possible !

A +

[Galdon]

Je viens de chercher "Copying to tmp table" dans le moteur de recherche interne de ce forum :

http://www.developpez.net/forums/d98...-surtout-vues/
http://www.developpez.net/forums/d86...ichiers-mysql/
http://www.developpez.net/forums/d75...ter-jointures/
http://www.developpez.net/forums/d56...le-temporaire/
http://www.developpez.net/forums/d19...lus-courantes/
http://www.developpez.net/forums/d28...ation-requete/
http://www.developpez.net/forums/d11...le-manque-ram/
http://www.phpbb.com/community/viewtopic.php?t=369256
http://www.developpez.net/forums/d11...requete-phpbb/

Et j'ai remarqué que dans beaucoup de ces topics, des tables phpbb sont impliquées. Ça ne va pas être simple je crois, je vais faire des tests avec les tables phpbb_users et phpbb_topics, en ajoutant ou en supprimant des index, en changeant de moteur...

[ericd69]

Salut,

si ton champ forum_id n'est pas indexé, ça peut expliquer la lenteur mais normalement phpbb le fait. j'avoue que phpbb est plutot pas très bien programmé sur certains trucs

Il semble que mysql refasse la recherche de la sous requête (d'où le re-remplissage de la table temporaire autan de fois) pour chaque itération de la requête principale. Le problème c'est que c'est pas documenté.

Au pire tu peux faire la requête temporaire à la main et de faire ta sous requête avec elle du genre:

Code sql :

1
2
3
4
5
6
7
8
CREATE tempory TABLE tmp
SELECT DISTINCT topic_poster AS user_id
	FROM phpbb_topics
	WHERE forum_id = 33;
SELECT user_id, username, user_type, user_posts
FROM phpbb_users
WHERE user_id IN(
SELECT user_id FROM tmp);

Idéalement le mieux est de faire un procédure stockée qui encapsule tout ça :

Code sql :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
USE bidule;
DROP procedure IF exist get_poster;
delimiter |
CREATE procedure get_poster(IN f_id int(4))
begin
CREATE tempory TABLE tmp
SELECT DISTINCT topic_poster AS user_id
	FROM phpbb_topics
	WHERE forum_id = f_id;
SELECT user_id, username, user_type, user_posts
FROM phpbb_users
WHERE user_id IN(
SELECT user_id FROM tmp);
DROP TABLE tmp;
end|
delimiter ;

Bidule est le nom de la bd où tu as mis phpbb. Pour le résultat:

Code sql :

call get_poster(33);

[Galdon]

Merci, sinon on m'a proposé une requête qui fonctionne impec sur un autre forum :

Code :

1
2
3
4
5
6
7
8
SELECT phpbb_users.user_id, username, user_type, user_posts
FROM phpbb_users
INNER JOIN (
    SELECT DISTINCT topic_poster
      FROM phpbb_topics
    WHERE forum_id = 33
) x
ON x.topic_poster = phpbb_users.user_id

Apparemment le problème viendrait de l'analyseur de requête MySQL qui a parfois de drôles d'interprétations.

[ericd69]

oui c'est assez mal documenté

faut parfois forcer les choses, comme l'obliger à choisir tel index...

en tout cas, l'utilisation de procédure stockée est bénéfique en terme de quantité de données échangées entre sgbd et langage interrogeant, et ça simplifie l'écriture de ton code coté applicatif en permettant des choses très compliquée...