Si vous lisez régulièrement les billets de ce blog (ce que j’espère secrètement), vous savez que j’essaie dans la mesure du possible de parler de choses qui servent au quotidien à la fois pour les développeurs Web utilisant PHP mais aussi pour les personnes qui travaillent avec des bases de données (relationnelles ou pas). C’est ainsi qu’aujourd’hui nous allons nous focaliser sur un type de requête relativement souvent utilisé et qui a pour but de faire « remonter » la ligne (ou les lignes) contenant la plus grande valeur d’un champ ! C’est parti ! Amusons-nous avec nos joyeux élèves !

Notre jeu de données

Soit la table eleves :

mysql> select * from eleves;
+----+-----------+------------+------+
| id | nom       | prenom     | note |
+----+-----------+------------+------+
|  1 | Ferrandez | Sébastien  |   10 |
|  2 | Ferrandez | Christophe |   12 |
|  3 | Gérard    | Olivier    |    9 |
|  4 | Gérard    | olivier    |   15 |
|  5 | Ferrandez | Christophe |   17 |
|  6 | Ferrandez | Sébastien  |    8 |
|  7 | Gérard    | Jean       |    4 |
|  8 | Django    | Marina     |   17 |
+----+-----------+------------+------+

Le but

Je souhaite connaitre la ou les personne(s) qui ont obtenu la note la plus haute.

Les façons de faire

A l’instinct…

Je sais qu’il est question de MAX quelque part, je serais tenté de faire :

SELECT max(note), nom, prenom FROM eleves;

Mais là, deux problèmes se posent…

• 1 : je n’ai qu’un enregistrement alors que deux élèves ont la meilleure note
• 2 : en face de cette meilleure note, je n’ai pas le bon couple nom/prénom

La jointure externe

Ici, nous allons nous baser sur la présence d’un marqueur NULL pour remonter les enregistrements qui nous intéressent. Nous disons en substance « Je veux le nom et le prénom des élèves pour lesquels il n’existe pas de note qui soit plus grande ».

SELECT s1.note, s1.nom, s1.prenom
FROM eleves s1
LEFT JOIN eleves s2 ON (s1.note < s2.note)
WHERE s2.id IS NULL

Cette solution là ramène bien les bons résultats :

mysql> SELECT s1.note, s1.nom, s1.prenom
    -> FROM eleves s1
    -> LEFT JOIN eleves s2 ON (s1.note < s2.note)
    -> WHERE s2.id IS NULL;
+------+-----------+------------+
| note | nom       | prenom     |
+------+-----------+------------+
|   17 | Ferrandez | Christophe |
|   17 | Django    | Marina     |
+------+-----------+------------+
2 rows in set (0.00 sec)

Le temps d’exécution est de l’ordre de la milliseconde (inférieur à une dizaine de millisecondes), nous n’avons pas indexé quelque champ que ce soit donc tout semble correct. Mais votre jeu de données grossissant, vous allez vite vous rendre compte que cette solution n’est pas viable ! Sur une table de 100000 enregistrements, une telle requête (avec le champ note indexé !) prend plus de 17 minutes à s’exécuter…Nous serions bons pour le peloton d’exécution avec un tel temps d’exécution !

La sous-requête

Il nous reste la solution de la sous-requête, qui va s’avérer dans notre cas la plus performante :

SELECT  note, nom, prenom
FROM   eleves
WHERE  note=(SELECT MAX(note)
              FROM eleves)

Sur notre set de 100000 enregistrements, avec le champ note indexé, elle met 0.04 secondes à s’exécuter, nous sommes loin des 17 minutes de la jointure externe, qui semblait bien fonctionner sur un petit nombre d’enregistrements. Un EXPLAIN nous montre que

+----+-------------+--------+------+---------------+------+---------+-------+------+------------------------------+
| id | select_type | table  | type | possible_keys | key  | key_len | ref   | rows | Extra                        |
+----+-------------+--------+------+---------------+------+---------+-------+------+------------------------------+
|  1 | PRIMARY     | eleves | ref  | note          | note | 1       | const |    2 | Using where                  |
|  2 | SUBQUERY    | NULL   | NULL | NULL          | NULL | NULL    | NULL  | NULL | Select tables optimized away |
+----+-------------+--------+------+---------------+------+---------+-------+------+------------------------------+
2 rows in set (0.00 sec)

« Select tables optimized away » signifie que notre sous-requête ne contient qu’une fonction agrégative (sans groupement) et que l’optimiseur MySQL a déterminé qu’il ne renverra qu’une seule et unique valeur.

En conclusion, nous voyons que la sous-requête est la plus adaptée à notre cas ici. Pourquoi ne pas tenter un petit GROUP_CONCAT ordonné, également ? La requête reste performante et les résultats sont présentés de manière « agréable »…

mysql> SELECT note, GROUP_CONCAT( nom,  ' ', prenom
    -> ORDER BY nom ) AS  "Eleves"
    -> FROM eleves
    -> WHERE note = ( 
    -> SELECT MAX( note ) 
    -> FROM eleves );
+------+------------------------------------+
| note | Eleves                             |
+------+------------------------------------+
|   17 | Django Marina,Ferrandez Christophe |
+------+------------------------------------+

N’écoutez pas les oiseaux de malheur qui vitupèrent constamment les sous-requêtes, souvent ils n’en n’ont jamais fait une seule de leur vie et propagent des racontars qu’ils lisent ça et là sur des forums généralistes où prolifèrent leurs congénères aux avis biaisés ou erronés…Dans certains cas, ces sous-requêtes se révéleront bien plus efficaces que des jointures internes ou externes hasardeuses…Votre seule jauge quand vous écrivez une requête c’est EXPLAIN et pas le collègue ignare qui propage des légendes urbaines informatiques !