PHP / mySQL : Extraire N lignes aléatoirement

L’extraction de lignes prises hasard dans une table MySQL est utile lorsque l’on souhaite afficher aléatoirement une des lignes de la base de données sur la page d’accueil. Par exemple, pour un site d’actualité cette méthode permet d’extraire un article au hasard ou pour une actualité d’extraire les 5 actualités dans la même catégorie. Cette problématique pourtant simple à expliquer nécessite une réflexion quant à sa mise en place.

Utilisation la fonction RAND  de mySQL pour une extraction aléatoire ?

La première solution qui vient à l’esprit est de trier des lignes de résultat de façon aléatoire. En utilisant la fonction RAND de mySQL, un nombre aléatoire va être placé en fin de chaque ligne extraite. Il suffit de trier le résultat de la requête et de prendre uniquement les N premières lignes.

SELECT id, titreArticle, texteArticle FROM ARTICLE ORDER BY RAND() ;

En théorie cette implémentation simple et rapide fonctionne. C’est d’ailleurs la meilleure méthode si la table contenant dont les articles est petite (moins de 100 articles) et n’est pas amenée à grossir par la suite.

Il faut savoir que cette solution est extrêmement gourmande en ressource machine si la table contient plus de quelques dizaines de lignes. Pour comprendre pourquoi voici quelques explications sur la façon dont MySQL va traiter cette requête :

• Toutes les lignes de la table vont être extraites. Pour extraire 1 article au hasard d’une table comportant plus de 100.000 enregistrements, mySQL va devoir générer 100.000 nombres aléatoires puis ensuite trier un tableau de 100.000 entrées.
• Pour chaque ligne MySQL va appeler la fonction RAND pour ajouter un champ aléatoire en fin de ligne. La fonction RAND va être recalculé à chaque appel, il n’y a pas de cache, la requête va être totalement exécutée  à chaque chargement de la page.
• Le tri va porter sur l’ensemble des lignes, la durée du tri va exploser au fur et à mesure que le nombre de ligne augmente. L’utilisation des index sur la table n’apporte aucune amélioration, en effet l’utilisation de RAND annule tous les bénéfices puisque il n’y a aucune mise en cache et donc les lignes sont reconstruites à chaque nouvelle requête.

Il existe des exemples de code sur d’autres sites qui proposent des solutions pour utiliser uniquement mySQL sans PHP, malheureusement les exemples proposés ne gèrent pas les tables avec des enregistrements à trous ou alors la probabilité de chaque entrée n’est pas identique. L’extraction d’un nombre aléatoire arrondi à l’identifiant précédent va favoriser les enregistrements précédant de grandes plages d’identifiants non attribués.

Ci-dessous un exemple extrême pour comprendre pourquoi les fonctions FLOOR et CEIL ne permettent d’apporter une réponse à notre besoin dans le cas de tables ayant des identifiants discontinus.

L’utilisation d’un langage de script est nécessaire pour obtenir un comportement totalement aléatoire des lignes.

Extraction totale sur l’index et récupération de N lignes au hasard avec PHP

La solution pour ne pas dégrader le temps de réponse pour l’utilisateur, il va falloir découper le programme en plusieurs étapes et déporter une partie des traitements dans le script PHP afin de travailler sur des données réduites et non pas sur l’ensemble des champs de la table.

Afin de ne pas allonger les temps de réponse avec des calculs inutiles, la seule solution va consister à rechercher les lignes en 3 étapes :

• Extraction de tous les identifiants de la table. Normalement si la table est bien conçue, les champs de l’identifiant sont indexés. Les sélections uniquement sur index réduisent les temps de traitement.
• Récupération de N identifiants de lignes grâce à la fonction de PHP.
• Extraction complète des lignes sélectionnées

// On sélectionne 5 articles parmi tous
$sql = "SELECT id FROM ARTICLE ";
$data = $db->query($sql) or die ($db->error);

On dispose maintenant dans $data d’un pointeur vers le premier identifiant de la table des articles. Pour sélectionner N lignes au hasard on va utiliser la fonction data_seek qui permet de positionner le pointeur de lecture en fonction d’un offset déterminé aléatoirement.

$i = 0;
$N = 10 ; // Nombre de lignes aléatoires à extraire
$tabId = array(); // Stockage des identifiants des lignes à extraire
while ($i < $N) {
  $alea = rand(0, $data->num_rows - 1); // On tire une ligne au hasard
  $data->data_seek($alea);
  $result = $data->fetch_array(MYSQLI_ASSOC);
  $temId = 0;
  // Vérification que l'indice n'a pas déjà été choisi
  for ($j = 0; $j < $i; $j++) {
    if ($tabId[$j] == $result['id']) {
      $temId = 1;
    }
  }
  // Si la valeur n'est pas présente dans les nombres aléatoires déjà extraits :
  // On la sauvegarde. Sinon on en génère une nouvelle.
  if ($temId == 0) {
    $tabId[$i] = $result['id'];
    $i++;
  }
}

La fonction rand de PHP va déterminer un nombre aléatoire entre 0 et le nombre de lignes total de la table. Le nombre aléatoire renvoyé par PHP ne peut pas être utilisé directement comme identifiant, en effet il est courant d’avoir des lignes pour lesquelles l’id n’existe pas. Notamment en cas de suppression de lignes. Pour cela, on récupère directement l’identifiant de la ligne en allant lire l’enregistrement correspondant grâce à la fonction PHP data_seek.

Il faut ensuite s’assurer que le nombre aléatoire généré n’a pas déjà été choisi. Sans ce contrôle, il sera possible d’avoir des doublons au niveau des articles en rapport avec l’article principal. Pour vérifier l’unicité des lignes extraites, les identifiants sont stockés dans un tableau au fur et à mesure. A chaque nouvelle lecture de ligne, on va vérifier que l’identifiant ne fait pas partie du tableau. Si l’identifiant existe déjà, on ne le stocke pas et on en génère un nouveau. A la fin de cette étape, on va se retrouver avec un tableau tabId[] contenant N identifiants équiprobables  de lignes extraits sur l’ensemble des enregistrements de la table.

Il ne reste maintenant plus qu’à lire l’ensemble des champs de l’article à afficher correspondant aux identifiants de lignes contenus dans le tableau tabId[].

$i = 0;
while ($i < $N) {
  $sql = "SELECT A.id, titreArticle, DATE_FORMAT(dateArticle, '%d/%m/%Y') AS dateArticle, ";
  $sql.= "texteArticle, B.titreCategorie, B.idCategorie ";
  $sql.= "FROM ARTICLE A, ARTICLE_CATEGORIE B ";
  $sql.= "WHERE A.categorieArticle=B.idCategorie AND A.id=$tabId[$i]";
  $data = $db->query($sql) or die ($db->error);
  $result = $data->fetch_array(MYSQLI_ASSOC);
 
  // Affichage de l’article…
 
}

La boucle va parcourir l’ensemble des identifiants dans tabId[]. C’est ensuite à vous à déterminer l’utilisation que vous souhaitez en faire.

En résumé

Ce besoin d’apparence simple, va nécessiter l’utilisation de plusieurs étapes afin d’arriver à un résultat correct en conservant des temps de réponses acceptables pour vos visiteurs. L’extraction aléatoire ne fait pas partie des fonctionnalités de mySQL c’est la raison pour laquelle l’utilisation d’un tableau PHP est indispensable.