Discussion :

[alband85]

Bonjour,

À titre « ludique », je souhaite lancer une requête affichant le classement des statistiques des commentaires postés sur un blog Wordpress au cours des six derniers mois.

À ce jour, j'ai cette requête, particulièrement sous-optimisée :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
SELECT   wc1.comment_author AS AUTHOR, 
         count(*) AS COUNT_AUTHOR, 
         (count(*)/(SELECT count(*) FROM wp_comments))*100 AS RATE, 
         MAX(wc1.comment_date) AS LAST_RAW, 
         DATE_FORMAT(MAX(wc1.comment_date), '%d/%m/%Y') AS LAST 
  FROM   wp_comments as wc1 
 WHERE   wc1.comment_author IN ( 
               SELECT   comment_author 
                 FROM   wp_comments AS wc2 
                WHERE   wc2.comment_date >= DATE_ADD(NOW(), INTERVAL -6 MONTH)  
        ) 
 GROUP   BY AUTHOR 
 ORDER   BY COUNT_AUTHOR DESC, LAST_RAW DESC

Il faut environ 5 secondes pour renvoyer une quinzaine de lignes et il y a moins de 1500 lignes dans la table wp_comments.

J'ai bien regardé les transformations usuelles de SQLPro (http://sqlpro.developpez.com/cours/optimiser/#L9), mais je n'arrive pas à lier les deux exemples fournis à mon cas.

Pour commencer, est-il possible de dé-corréler cette requête ?
Et si oui, quelle est la marche à suivre pour y parvenir ?

À titre indicatif pour les non-habitués de Wordpress, la structure de la table :

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
19
20
21
22
CREATE TABLE `wp_comments` (
  `comment_ID` bigint(20) unsigned NOT NULL auto_increment,
  `comment_post_ID` bigint(20) unsigned NOT NULL default '0',
  `comment_author` tinytext NOT NULL,
  `comment_author_email` varchar(100) NOT NULL default '',
  `comment_author_url` varchar(200) NOT NULL default '',
  `comment_author_IP` varchar(100) NOT NULL default '',
  `comment_date` datetime NOT NULL default '0000-00-00 00:00:00',
  `comment_date_gmt` datetime NOT NULL default '0000-00-00 00:00:00',
  `comment_content` text NOT NULL,
  `comment_karma` int(11) NOT NULL default '0',
  `comment_approved` varchar(20) NOT NULL default '1',
  `comment_agent` varchar(255) NOT NULL default '',
  `comment_type` varchar(20) NOT NULL default '',
  `comment_parent` bigint(20) unsigned NOT NULL default '0',
  `user_id` bigint(20) unsigned NOT NULL default '0',
  PRIMARY KEY  (`comment_ID`),
  KEY `comment_approved` (`comment_approved`),
  KEY `comment_post_ID` (`comment_post_ID`),
  KEY `comment_approved_date_gmt` (`comment_approved`,`comment_date_gmt`),
  KEY `comment_date_gmt` (`comment_date_gmt`)
);

Merci d'avance et bon week-end !

Alban

[Antoun]

quel est le SGBD ? MySQL ?

[CinePhil]

Pourquoi mettre une sous-requête dans le WHERE ?

La restriction WHERE porte sur chaque ligne de la table donc tu devrais pouvoir directement faire :

Code :

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

WHERE   wc2.comment_date >= DATE_ADD(NOW(), INTERVAL -6 MONTH)

Ces deux colonnes vont donner le même résultat, avec certes une mise en forme différente :

Code :

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

1
2
MAX(wc1.comment_date) AS LAST_RAW, 
         DATE_FORMAT(MAX(wc1.comment_date), '%d/%m/%Y') AS LAST

Est-ce bien utile ?

[alband85]

Avant toute chose, toutes mes excuses pour le temps de réponse. J'avais oublié ce fil...

Effectivement, c'est du MySQL.

A propos des dates, je crois que j'ai fait ça parce que la date mise en forme ne permettait pas le tri suivant la date (après, ça fait longtemps... c'est possible aussi que ça soit la conséquence d'un "je code à 3h du mat' et je ne devrais pas"). Je referai mes tests sur ce point.

Le problème qui se pose avec la requête imbriquée :
- je veux le total des messages posté depuis le début des temps ;
- mais seulement pour les auteurs ayant posté au cours des 6 derniers mois.
Si je n'utilise que wc1, si je filtre sur la date, je n'ai que le nombre de messages postés au cours des six derniers mois.
Par contre, là, tout de suite (sans avoir les yeux en face des trous ni d'accès à la base pour tester), je me dis qu'une belle jointure devrait fonctionner :

Code :

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

1
2
3
INNER   JOIN wp_comments AS wc2
          ON wc2.comment_author = wc1.comment_author
         AND wc2.comment_date >= DATE_ADD(NOW(), INTERVAL -6 MONTH)

Je teste ça et je reviens donner mes résultats.

D'ailleurs, le

Code :

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

wc2.comment_date >= DATE_ADD(NOW(), INTERVAL -6 MONTH)

est plus à sa place dans la jointure ou dans le where ?

Merci pour vos réponses (qui me forcent à réfléchir ) !

Alban

[alband85]

Héhé, maintenant que j'ai dormi un peu, je sais pourquoi j'avais fait mon wc1.comment_author IN (SELECT...).

Si je fais une jointure comme ceci :

Code :

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

1
2
3
INNER   JOIN wp_comments AS wc2
          ON wc2.comment_author = wc1.comment_author
         AND wc2.comment_date >= DATE_ADD(NOW(), INTERVAL -6 MONTH)

Je ressors pour chaque message d'un auteur donné tous les autres messages de ce même auteur (donc l'auteur se retrouve avec un taux de messages de 12554%... ce qui est un chouïa trop élevé).

C'est là que je me rend compte que ma requête est peut-être mal fichue.
En l'exprimant en français (à peu près) correct, ça donnerait :

Afficher le nombre total de commentaires et le pourcentage qu'ils représentent en n'affichant que les auteurs qui ont posté au cours des six derniers mois (mais les statistiques ne sont pas limitées à ces six derniers mois).

C'est terrible parce que ça a l'air tout bête comme requête, et pourtant...

[CinePhil]

Afficher le nombre total de commentaires et le pourcentage qu'ils représentent en n'affichant que les auteurs qui ont posté au cours des six derniers mois (mais les statistiques ne sont pas limitées à ces six derniers mois).

Si je comprends bien, tu souhaites afficher, par auteur ayant posté au cours des 6 mois :
- leur nombre total de commentaires depuis toujours ;
- le pourcentage de ce nombre par rapport au total des commentaires postés depuis toujours.

C'est ça ?
Alors à mon avis ta requête de base semble bonne mais il manque peut-être un DISTINCT dans la sous-requête du WHERE :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
SELECT   wc1.comment_author AS AUTHOR, 
         count(*) AS COUNT_AUTHOR, 
         (count(*)/(SELECT count(*) FROM wp_comments))*100 AS RATE, 
         MAX(wc1.comment_date) AS LAST_RAW, 
         DATE_FORMAT(MAX(wc1.comment_date), '%d/%m/%Y') AS LAST 
  FROM   wp_comments AS wc1 
 WHERE   wc1.comment_author IN ( 
               SELECT DISTINCT comment_author 
                 FROM   wp_comments AS wc2 
                WHERE   wc2.comment_date >= DATE_ADD(NOW(), INTERVAL -6 MONTH)  
        ) 
 GROUP   BY AUTHOR 
 ORDER   BY COUNT_AUTHOR DESC, LAST_RAW DESC

[alband85]

Voilà, tu as bien compris

Mon problème essentiel vient des performances.
Sachant que

Code :

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

SELECT count(*) FROM wp_comments

renvoie seulement 1407 ( la volumétrie n'est donc pas excessive).

Du coup, je me demande s'il n'y a pas moyen d'optimiser : ça me choque qu'on ne puisse pas faire mieux que 5 secondes pour ce genre de requête.

Au final, il semble que le DISTINCT consomme plus qu'un IN avec des doublons :

• Avec DISTINCT : Affichage des enregistrements 0 - 13 (14 total, Traitement en 6.9845 sec.)
• Sans DISTINCT : Affichage des enregistrements 0 - 13 (14 total, Traitement en 4.9609 sec.)

[pacmann]

Salut !

Je trouve ça un peu choquant de mettre DISTINCT dans un IN...
... et je trouve encore plus choquant que MySQL prenne plus de temps avec le distinct ! (pour moi il devrait se rendre compte que ça ne change rien et faire exactement la même chose)

Bon, pour la requête, 5 secondes, c'est effectivement lent (de toutes façons, à partir du moment où une requête est plus rapide à faire dans Excel, c'est qu'il y a un vrai problème)...

Il faut le plan d'exécution !

Pour 1400 lignes, même sans indexe la requête devrait être instantannée...
(à moins que chaque ligne pèse 2 go )

[Antoun]

MySQL est nul à chier avec le IN... Il y aurait beaucoup à gagner en remplaçant le IN par une jointure sur une table virtuelle (une sous-requête dans le FROM).

[alband85]

On commence par le plan d'exécution... mais qui n'a pas l'air d'en dire beaucoup :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
EXPLAIN
SELECT   wc1.comment_author AS AUTHOR, 
         count(*) AS COUNT_AUTHOR, 
         (count(*)/(SELECT count(*) FROM wp_comments))*100 AS RATE, 
         MAX(wc1.comment_date) AS LAST_RAW, 
         DATE_FORMAT(MAX(wc1.comment_date), '%d/%m/%Y') AS LAST 
  FROM   wp_comments AS wc1 
 WHERE   wc1.comment_author IN ( 
               SELECT   comment_author 
                 FROM   wp_comments AS wc2 
                WHERE   wc2.comment_date >= DATE_ADD(NOW(), INTERVAL -6 MONTH)  
        ) 
 GROUP   BY AUTHOR 
 ORDER   BY COUNT_AUTHOR DESC, LAST_RAW DESC

Code :

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

1
2
3
4
5
6
7
8
+----+--------------------+-------+------+---------------+------+---------+------+------+----------------------------------------------+
| id | select_type        | table | type | possible_keys | key  | key_len | ref  | rows | Extra                                        |
+----+--------------------+-------+------+---------------+------+---------+------+------+----------------------------------------------+
|  1 | PRIMARY            | wc1   | ALL  | NULL          | NULL | NULL    | NULL | 1407 | Using where; Using temporary; Using filesort |
|  3 | DEPENDENT SUBQUERY | wc2   | ALL  | NULL          | NULL | NULL    | NULL | 1407 | Using where                                  |
|  2 | SUBQUERY           | NULL  | NULL | NULL          | NULL | NULL    | NULL | NULL | Select tables optimized away                 |
+----+--------------------+-------+------+---------------+------+---------+------+------+----------------------------------------------+
3 rows in set (0.00 sec)

@Antoun : je vois ce que tu veux dire par jointure sur une table virtuelle... mais je ne vois pas comment tu l'imagines ici. Tu peux en dire quelques mots ?

Merci à tous les deux !