Forum d'entraide PHPFrance

Bonjour à tous,

Voilà, pour les 3 requêtes de mon forum en cours d'élaboration, j'ai des requêtes assez conséquentes, 42 lignes pour l'affichage de l'index du forum, 32 pour l'affichage de tous les posts d'une catégorie, et 17 et 18 lignes por l'affichage de tous les posts d'un sujet.

Elles contiennent pas mal de sous requêtes.

J'ai téléchargé un forum phpbb, pour voir leurs requêtes, et je n'en ai pas vu d'aussi lourdes que les miennes

Etant donné que mes tables sont pour l'instant vides, ça s'exécute assez rapidement 0.05 sec par une console MySQL.

Ma question :

Y'a t-il un moyen d'évaluer dès à présent la rapidité des requêtes futures lorsque mes tables seront chargées (plusieurs milliers de lignes) ?

Par exemple avec un ratio par rapport à mes requêtes actuelles ?

Merci de vos réponses

Il n'existe pas de critère unique pour mesurer les performances d'une requête. Commence par te familiariser avec l'optimisation de tes requêtes grâce au chapître Optimisation des commandes SELECT et autres requêtes du manuel de MySQL, particulièrement avec la fonction EXPLAIN.

Quelques indicateurs, dans EXPLAIN:

regarde le type de jointure de chaque table, si ce n'est pas eq_ref, ref ou à la rigueur range alors tu as peut-être besoin d'un index plus adapté. Si ta requête n'est sensée renvoyer qu'un seul enregistrement alors le type de jointure devrait être const, ou system.
regarde dans la colonne rows, ce nombre est une estimation du nombre d'enregistrements à examiner. Plus c'est bas mieux c'est. Si le nombre est très différent du nombre d'enregistrements renvoyés par la requête alors tu as peut-être un problème dans le design de ta requête, ou dans les indices. (n'oublie pas d'exécuter ANALYZE TABLE ou OPTIMIZE TABLE de temps à autres !)
dans la colonne Extra, "Using filesort" c'est pas terrible et "Using temporary" non plus, mais on ne peut pas toujours les éliminer. Et puis ça dépend de la taille du résultat aussi... Tu peux éliminer le premier en créant un index sur la colonne de tri (Comment MySQL optimise ORDER BY)

Tu peux avoir une idée du "coût" de la dernière requête exécutée grâce à

Code : Tout sélectionner

SHOW STATUS LIKE 'Last_query_cost'

...mais là encore ce n'est pas un critère absolu. En dessous de 50 aucun soucis, en dessous de 500 il vaut mieux que tu aies un bon index et au dessus de 5000 c'est signe que la requête peut être mal formée. (à part sur certaines requêtes lourdes utilisées uniquement pour de la maintenance). Attention, aux dernières nouvelles ça ne fonctionne qu'avec les requêtes de type SELECT.

Dernier conseil, chaque fois que cela est possible il est recommandé de se passer des sous-requêtes avec [toutes] les [...] versions de MySQL. Bon, j'ai un peu détourné le titre du chapître mais les jointures sont plus rapides que les sous-requêtes dans pratiquement tous les cas sous MySQL, donc ça reste un bon conseil même si ta version de MySQL supporte les sous-requêtes.

Ouch !!

Tout ce dont tu viens de me parler, je n'en connaissais pas la moindre chose.

Je viens de faire la requête SHOW STATUS LIKE 'Last_query_cost', et j'obtiens 1.199

Par contre avec EXPLAIN, j'obtiens :

Code : Tout sélectionner

+----+--------------------+------------+--------+-------------------+-----------+---------+--------------+------+-----------------------------------------------------------+
| id | select_type        | table      | type   | possible_keys     | key       | key_len | ref          | rows | Extra                                                     |
+----+--------------------+------------+--------+-------------------+-----------+---------+--------------+------+-----------------------------------------------------------+
|  1 | PRIMARY            | C          | ALL    | NULL              | NULL      | NULL    | NULL         |    2 | Using temporary; Using filesort                           |
|  1 | PRIMARY            | SC         | ALL    | id_cat            | NULL      | NULL    | NULL         |    4 |                                                           |
|  1 | PRIMARY            | S          | ref    | id_ss_cat         | id_ss_cat | 4       | projet.SC.id |    6 |                                                           |
|  1 | PRIMARY            | R          | ref    | id_sujet          | id_sujet  | 4       | projet.S.id  |    4 | Using index                                               |
| 21 | DEPENDENT SUBQUERY | f_reponse  | ref    | id_sujet          | id_sujet  | 4       | func         |    1 | Using where; Using index; Using filesort                  |
| 20 | DEPENDENT SUBQUERY | m_connect  | eq_ref | PRIMARY           | PRIMARY   | 4       | func         |    1 | Using where                                               |
| 19 | DEPENDENT SUBQUERY | m_connect  | eq_ref | PRIMARY           | PRIMARY   | 4       | func         |    1 | Using where                                               |
| 18 | DEPENDENT SUBQUERY | f_topic_lu | ALL    | NULL              | NULL      | NULL    | NULL         |   30 | Using where                                               |
| 17 | DEPENDENT SUBQUERY | f_topic_lu | ALL    | NULL              | NULL      | NULL    | NULL         |   30 | Using where                                               |
| 16 | DEPENDENT SUBQUERY | f_reponse  | ref    | id_sujet          | id_sujet  | 4       | func         |    1 | Using where                                               |
| 15 | DEPENDENT SUBQUERY | f_reponse  | ref    | id_sujet          | id_sujet  | 4       | func         |    1 | Using where; Using index; Using filesort                  |
| 14 | DEPENDENT SUBQUERY | f_sujet    | eq_ref | PRIMARY           | PRIMARY   | 4       | func         |    1 | Using where; Using index                                  |
| 13 | DEPENDENT SUBQUERY | f_reponse  | ref    | id_sujet          | id_sujet  | 4       | func         |    1 | Using where                                               |
| 12 | DEPENDENT SUBQUERY | f_sujet    | eq_ref | PRIMARY           | PRIMARY   | 4       | func         |    1 | Using where                                               |
| 11 | DEPENDENT SUBQUERY | f_sujet    | eq_ref | PRIMARY           | PRIMARY   | 4       | func         |    1 | Using where                                               |
| 10 | DEPENDENT SUBQUERY | f_reponse  | ref    | id_sujet          | id_sujet  | 4       | func         |    1 | Using where                                               |
|  9 | DEPENDENT SUBQUERY | f_reponse  | ref    | id_sujet          | id_sujet  | 4       | func         |    1 | Using where; Using filesort                               |
|  8 | DEPENDENT SUBQUERY | f_sujet    | eq_ref | PRIMARY           | PRIMARY   | 4       | func         |    1 | Using where                                               |
|  7 | DEPENDENT SUBQUERY | f_reponse  | ref    | id_sujet          | id_sujet  | 4       | func         |    1 | Using where                                               |
|  6 | DEPENDENT SUBQUERY | f_reponse  | ref    | id_sujet          | id_sujet  | 4       | func         |    1 | Using where; Using filesort                               |
|  5 | DEPENDENT SUBQUERY | f_sujet    | eq_ref | PRIMARY           | PRIMARY   | 4       | func         |    1 | Using where                                               |
|  4 | DEPENDENT SUBQUERY | f_reponse  | ref    | id_sujet          | id_sujet  | 4       | func         |    1 | Using where                                               |
|  3 | DEPENDENT SUBQUERY | S          | ref    | PRIMARY,id_ss_cat | id_ss_cat | 4       | func         |    1 | Using where; Using index; Using temporary; Using filesort |
|  3 | DEPENDENT SUBQUERY | R          | ref    | id_sujet          | id_sujet  | 4       | projet.S.id  |    1 | Using where                                               |
|  2 | DEPENDENT SUBQUERY | f_sujet    | ref    | id_ss_cat         | id_ss_cat | 4       | func         |    1 | Using where; Using index                                  |
+----+--------------------+------------+--------+-------------------+-----------+---------+--------------+------+-----------------------------------------------------------+

Il y a quelques ALL.

Ok pour préférer les jointures aux sous-requêtes, mais le problèmes, C'est que j'ai déjà des jointures sur 4 tables.

Et mes sous-requêtes en fait ne sont pas dans le WHERE, mais dans le SELECT.

Merci, je vais lire plus en détail les liesn que tu m'as fourni.

Bon, ben déjà on peut voir que ta requêtes comporte près de 25 jointures, c'est au moins 20 de plus que ce dont tu as besoin. Tu utilises également un nombre incalculable de sous-requêtes ce que me fait penser que tu as un gros problème de design, pourrais-tu poster la requête qui a généré ce rapport stp ?

D'accord, mais attention les yeux

Code : Tout sélectionner

SELECT C.nom AS categorie, 
S.date AS suj_date,
SC.id AS id_soucat,
(SELECT MAX(id) FROM f_sujet WHERE id_ss_cat = id_soucat) AS suj_id,
(SELECT id_sujet FROM f_reponse R LEFT JOIN f_sujet S ON id_sujet = S.id WHERE id_ss_cat = id_soucat ORDER BY R.date DESC LIMIT 1) AS rep_id,
SC.nom AS soucat, 
SC.comment,
COUNT(DISTINCT(S.id)) AS tt_sujet,
COUNT(R.id) AS tt_rep,
COALESCE( IF( S.date >= COALESCE( (SELECT MAX(date) FROM f_reponse WHERE id_sujet = rep_id), 1), 
          (SELECT id_membre FROM f_sujet WHERE id = suj_id),
          (SELECT id_membre FROM f_reponse WHERE id_sujet = rep_id ORDER BY id DESC LIMIT 1) ), ' - ') 
    AS id_auteur,                
COALESCE(IF( S.date >= COALESCE((SELECT MAX(date) FROM f_reponse WHERE id_sujet = rep_id), 1), 
        (SELECT DATE_FORMAT(date, '%d/%m/%Y à %Hh%i') FROM f_sujet WHERE id = suj_id),
        (SELECT DATE_FORMAT(date, '%d/%m/%Y à %Hh%i') FROM f_reponse
            WHERE id_sujet = rep_id ORDER BY id DESC LIMIT 1 ) ), ' - ') 
    AS date_der,
COALESCE(IF( S.date >= COALESCE((SELECT MAX(date) FROM f_reponse WHERE id_sujet = rep_id), 1), 
        (SELECT nom FROM f_sujet WHERE id = suj_id),
        (SELECT nom FROM f_sujet WHERE id = rep_id ORDER BY id DESC LIMIT 1) ), ' - ')
    AS sujet,
COALESCE(IF( S.date >= COALESCE((SELECT MAX(date) FROM f_reponse WHERE id_sujet = rep_id), 1), 
        (SELECT id FROM f_sujet WHERE id = suj_id),
        (SELECT id_sujet FROM f_reponse 
            WHERE id_sujet = rep_id ORDER BY id DESC LIMIT 1) ), ' - ') 
    AS s_id,
COALESCE(IF( S.date >= COALESCE((SELECT MAX(date) FROM f_reponse WHERE id_sujet = rep_id), 1), 
      (SELECT COUNT(*) FROM f_topic_lu WHERE id_post = suj_id AND id_m_connect = " . protection($id_membre) . "),
      (SELECT COUNT(*) FROM f_topic_lu WHERE id_post = rep_id AND id_m_connect = " . protection($id_membre) . ") ), ' - ') 
    AS vu,   
(SELECT pseudo FROM m_connect WHERE id = id_auteur) AS auteur,
(SELECT groupe FROM m_connect WHERE id = id_auteur) AS groupe,
COALESCE( (SELECT id FROM f_reponse WHERE id_sujet = rep_id ORDER BY id DESC LIMIT 1), 0) AS ancre
FROM f_cat AS C
LEFT JOIN f_ss_cat AS SC
ON C.id = SC.id_cat
LEFT JOIN f_sujet AS S
ON SC.id = S.id_ss_cat
LEFT JOIN f_reponse AS R
ON S.id = R.id_sujet
GROUP BY SC.id
ORDER BY C.id, SC.id

Je n'ai pas trouvé comment faire autrement que me servir d'alias dans mes sous-requêtes pour obtenir ce que je voulais.

Je sais, c'est moche et lourd, mais j'ai vraiment galérer pour obtenir le résultat correct à l'affichage, surtout pour l'affichage de l'index.

Bon ben... euh, oublie cette requête et conçois-en une nouvelle. Je te recommande de te baser sur ce qu'utilise phpBB 2.0.x, regarde les champs topic_first_post_id et topic_last_post_id dans la table phpbb_topics. À partir de ces champs, tu peux obtenir toutes les infos liées au topic via les tables phpbb_posts et phpbb_users.

Je ne sais pas comment tu as appris SQL et si tu essaies intentionnellement d'avoir une base de données parfaitement normalisée, mais si c'est le cas alors oublie cette idée maintenant

Il est virtuellement impossible de faire fonctionner une forum sans un soupçon de dénormalisation (je dis "dénormalisation" même si ce point est débattable). En gros, dans une application web tu ne peux pas utiliser MAX() ou COUNT() dans une requête qui est exécutée sur chaque page, et encore moins dans une sous-requête exécutée pour chaque enregistrement.

Il te faut donc remplacer ces sous-requêtes par des compteurs dans ta table de topics. Par exemple, un compteur pour le nombre de messages d'un topic, et les IDs du premier et du dernier post. À partir de ces IDs tu peux joindre la table de posts et savoir quand un topic a été posté (date du premier message) ou le nom de la dernière personne à avoir répondu (en utilisant l'ID de l'utilisateur qui a posté le dernier message). Ou tu peux aller un peu plus loin et copier ces informations directement dans la table de topics. C'est plus rapide, mais il faut les mettre à jour un peu plus souvent.

La façon que tu me suggères de construire mes tables, c'était à peu près mon idée de départ, mais j'ai rencontré un problème qui me paraissait insoluble, et j'ai changé d'avis

Mon erreur la plus grossière a été de mettre l'auteur et son message dans la table sujet, je m'en aperçois maintenant.

C'est con, j'avais bien avancé, je commencais la partie admin, je suis un peu dépité

Tant pis, je recommence.

Enfin comme on dit, on tire l'enseignement de ses erreurs commises.

Merci beaucoup pour tous tes conseils.

Le SQL, euh, j'ai appris tout seul, un petit peu avec oracle lors d'un stage, et avec l'aide de quelques forumistes lorsque j'ai rencontré des problèmes, ça se voit tant que ça que je suis nul ?

...ça se voit tant que ça que je suis nul ?

Crois-tu qu'on était différents quand on a commencé ?

Enfin pour ce qui me concerne, je peux t'assurer que je suis pas né en connaissant le PHP ni le SQL

Merci, c'est gentil de me rassurer

Je sais pas au bout de combien de temps vous avez été performant et autonome, mais ça fait à peu près un an que je me suis mis au PHP et SQL, et j'ai toujours l'impression d'être un gros débutant.

Remarque j'ai quand même avancé, je viens de regarder mon 1er message pour me donner de l'espoir

Plus qu'une question de niveau c'est une question d'origine que je me posais. On ne voit jamais de débutant MySQL utiliser des sous-requêtes (surtout dans le SELECT) parce que les sous-requêtes sont relativement récentes sous MySQL. Pareil pour COALESCE() (et d'autant plus sur le résultat d'une sous-requête) parce qu'historiquement les utilisateurs de MySQL utilisaient IFNULL().

En fait, ta requête ressemble à ce qu'on peut voir chez des vieux administrateurs de bases de données à qui on a rabâché pendant leurs études qu'il fallait tout normaliser et qui ont appris le SQL à grands coups de sous-requêtes. Suivant comment tu le vois, tu peux prendre ça comme un compliment

Le conseil du jour, pour les débutant sous MySQL : n'utilise jamais de sous-requêtes, et surtout pas dans la clause SELECT. Si tu n'arrives pas à faire quelque chose avec une jointure c'est qu'il y a un problème (mais on a un forum pour ça hein). Et si tu fais une jointure sur plus de 4 tables, y'a de bonne chance pour qu'il y ait un problème aussi.

Ah donc, je code comme un vieux schnock

Sans sous-requêtes ça va être dur, c'est tellement pratique

Bonjour,

Voilà, j'ai refait la structure de mes tables pour mon forum.

Voici ce que j'ai fait :

Code : Tout sélectionner

f_categorie
+-------+-----------------+------+-----+---------+-------+
| Field | Type            | Null | Key | Default | Extra |
+-------+-----------------+------+-----+---------+-------+
| id    | int(2) unsigned | NO   | PRI |         |       |
| nom   | varchar(50)     | NO   |     |         |       |
+-------+-----------------+------+-----+---------+-------+
2 rows in set (0.06 sec)

f_sous_categorie
+--------------+-----------------+------+-----+---------+-------+
| Field        | Type            | Null | Key | Default | Extra |
+--------------+-----------------+------+-----+---------+-------+
| id           | int(2) unsigned | NO   | PRI |         |       |
| id_categorie | int(2) unsigned | NO   | MUL |         |       |
| nom          | varchar(150)    | NO   |     |         |       |
| commentaire  | varchar(255)    | NO   |     |         |       |
+--------------+-----------------+------+-----+---------+-------+
4 rows in set (0.05 sec)

f_topic_sujet
+--------------------+-----------------+------+-----+---------+----------------+
| Field              | Type            | Null | Key | Default | Extra          |
+--------------------+-----------------+------+-----+---------+----------------+
| id                 | int(6) unsigned | NO   | PRI | NULL    | auto_increment |
| id_sous_categorie  | int(2) unsigned | NO   | MUL |         |                |
| titre              | varchar(50)     | NO   |     |         |                |
| id_premier_message | int(8) unsigned | NO   | MUL |         |                |
| id_dernier_message | int(8) unsigned | NO   |     |         |                |
| nb_reponses        | int(4) unsigned | NO   |     | 0       |                |
| postit             | tinyint(1)      | YES  | UNI | NULL    |                |
| etat               | tinyint(1)      | YES  |     | NULL    |                |
+--------------------+-----------------+------+-----+---------+----------------+
8 rows in set (0.05 sec)

f_topic_message
+-----------------------+-----------------+------+-----+-------------------+----------------+
| Field                 | Type            | Null | Key | Default           | Extra          |
+-----------------------+-----------------+------+-----+-------------------+----------------+
| id                    | int(8) unsigned | NO   | PRI | NULL              | auto_increment |
| id_sujet              | int(6) unsigned | NO   | MUL |                   |                |
| id_membre_connect     | int(4) unsigned | NO   | MUL |                   |                |
| ip_membre             | char(15)        | NO   |     |                   |                |
| message               | text            | NO   |     |                   |                |
| date_envoi            | timestamp       | YES  |     | CURRENT_TIMESTAMP |                |
| nb_edition            | int(1)          | YES  |     | NULL              |                |
| date_derniere_edition | datetime        | YES  |     | NULL              |                |
+-----------------------+-----------------+------+-----+-------------------+----------------+
8 rows in set (0.01 sec)

f_topic_nombre_lecture
+----------+--------+------+-----+---------+-------+
| Field    | Type   | Null | Key | Default | Extra |
+----------+--------+------+-----+---------+-------+
| id_sujet | int(9) | NO   |     |         |       |
| lecture  | int(5) | NO   |     | 1       |       |
+----------+--------+------+-----+---------+-------+
2 rows in set (0.03 sec)

m_connect
+--------+--------------------------------------------------------------------+------+-----+---------+----------------+
| Field  | Type                                                               | Null | Key | Default | Extra          |
+--------+--------------------------------------------------------------------+------+-----+---------+----------------+
| id     | int(4) unsigned                                                    | NO   | PRI | NULL    | auto_increment |
| pseudo | char(20)                                                           | NO   | MUL |         |                |
| passwd | char(32)                                                           | NO   | MUL |         |                |
| email  | varchar(50)                                                        | NO   |     |         |                |
| groupe | enum('membre','modÚrateur','newseur','rÚdacteur','administrateur') | NO   |     | membre  |                |
+--------+--------------------------------------------------------------------+------+-----+---------+----------------+
5 rows in set (0.08 sec)

C'est déjà mieux, pour afficher l'index, je n'ai plus que 22 lignes et ceci sans sous requête, bon, j'ai une jointure sur 5 tables, et il me subsiste un count pour compter le nombre de sujet.

Par contre, pour afficher tous les sujets d'une catégorie de forum, je suis confronté à un petit problème, je ne sais pas comment faire pour différencier le createur du sujet, et le dernier posteur de ce sujet, sans faire de sous requête.

Voici pour l'instant ma requête :

Code : Tout sélectionner

SELECT S.id AS id_sujet,
titre,
postit,
etat,
pseudo AS createur,
pseudo AS dernier_posteur,
nb_reponses,
id_membre_connect,
M.id AS ancre,
DATE_FORMAT(date_envoi, \'%d/%m/%Y à %Hh%i\') AS date_envoi,
lecture,
SC.id AS id_sous_cat,
nom
FROM f_topic_sujet AS S
LEFT JOIN f_topic_message AS M
ON S.id = M.id_sujet
LEFT JOIN f_topic_nombre_lecture AS NL
ON S.id = NL.id_sujet
LEFT JOIN m_connect AS MC
ON id_membre_connect = MC.id
LEFT JOIN f_sous_categorie AS SC
ON id_sous_categorie = SC.id
WHERE SC.id=' . protect($_GET['sc']);

Merci !

Forum d'entraide PHPFrance

Tester la lourdeur de ses requêtes

Tester la lourdeur de ses requêtes