Optimiser un rand() MySQL en PHP

Vous qui me lisez en cachette jour et nuit, vous devez savoir que le blog a connu des ralentissements car, que voulez-vous, le succès mondial met un peu à genou mon hébergement. Un système de cache un peu rude a déjà été posé. Nouvel objectif : éliminer les appels à la fonction Random de MySQL !
Demander à PHP de jeter les dés
Notre bon vieux PHP sait très bien faire des tirages au sort. Normalement je devrais pour bien faire récupérer l'identifiant le plus haut de ma table pour connaître la limite haute : le dernier id de mon lot d'enregistrements. Mais c'est une requête en plus, et nous allons voir qu'on va laisser au hasard la possibilité de se planter. Je met donc un chiffre un peu au dessus de mon nombre de lignes en arrondissant. Alors je lui demande :$jeu_id = rand(0,7000);Là, je n'en prend qu'un. Pour en prendre un lot, passer tout ça dans un tableau et mettre le nombre d'occurrences que l'on veut pour avoir une liste de nombres. Ensuite on va passer ça à MySQL dans la requête à modifier en éliminant donc le ORDER BY RAND() et en indiquant donc "en dur" les identifiants à sélectionner :
$sql = 'SELECT DISTINCT * FROM latable WHERE machin_id IN ('.$jeuid.')';Et voilà ! Seulement voilà, dans le cas du blog, j'ai des trous dans mes identifiants, des billets qui ont été supprimés, etc ... Le tirage au sort de PHP peut donc demander à MySQL d'afficher des enregistrements qui n'existent pas en base. Comment faire ? Tout simplement effectuer plus de tirages au sort et ajouter une clause LIMIT à la requête SQL. Pour afficher 3 articles au hasard, on génère ainsi 5 chiffres au hasard. Cela laisse une marge d'erreur importante : il faut que PHP tombe plus de deux fois dans le lot sur des identifiants inexistants pour que l'affichage ne parvienne pas à sortir 3 données. La méthode est un peu roots pour le moment car je viens à peine de m'y pencher et je pense qu'on peut encore optimiser le code. Alors si vous avez des idées, suggestions ou commentaires, je suis partant. D'autant plus que j'ai maintenant à modifier des dizaines de portions de code un peu partout en appliquant ce patch ... allez, je me souhaite bon courage :)
Auteur : Simon Tripnaux
Blogueur lifestyle - Content manager & expert SEO. Mon job, rendre visible et lisible vos projets par les mots. Adepte de l'écriture depuis 1978.
Blogueur ? Auteur ? Rejoignez la rédaction !
Et aussi ...
Teaser : nouveau service !
Une petite annonce sous forme de teasing histoire de vous souffler à l'oreille qu'un nouveau service web arrive bientôt ... Ce sera sur Tribords.com (le domaine) et ce sera à la fois très amusant et très instructif. Ou alors pas...
InsideTags
Un nouveau blog tout frais : InsideTags ... Une ligne éditoriale pointue en vue, orientée développement et webdesign ! Et des ambitions qui en jettent un max : devenir le Smashing Magazine français ! Rien que ça, avec votre...
Ajoutez votre avis !
14 avis inspirés
La fonction random de PHP est réputée pas assez aléatoire dans l'absolu, ce qui n'est pas le cas de celle du moteur MySQL :)
Oui, il est de toutes façons clair que ce choix m'est imposé parce que j'ai un peu la main lourde avec ce genre de traitement des données, mais je pense que du point de vue du rendu HTML pour le SEO et la diversité offerte aux lecteurs, il y a des concessions à faire ... Ne pas renoncer aux possibilités de l'affichage aléatoire me semble primordial pour le site et tous les autres services. J'attends de toutes façons la validation des techs de mon hébergeur avant de me plonger là dedans ...
Si le but est d'optimiser, ça serait chouette d'arriver à se débarrasser du "SELECT * FROM..." qui peut s'avérer gourmand, en spécifiant les champs nécessaires - du genre "SELECT `id_post`, `titre_post`, `date_post`, [...] FROM..."
- si c'est faisable ;)
Pourquoi ne pas faire une fonction qui fait un rand unique, qui va chercher le select donc unique (une seule requete au lieu de 5 dans ton exemple)
En php tu test si tu as un résultat, si oui tu affiches, sinon tu rappelles ta fonction...le tout dans une boucle...
Leloup
Cette méthode-ci serait elle acceptable :
$jeu_id = rand(0,7000);
$sql = 'SELECT DISTINCT machin_id, champs1, champs2, [...] FROM latable WHERE machin_id >= '.$jeuid.' limit 0,3';
Cette formulation permet d'avoir les articles supérieurs ou égaux à la valeur tirée au sort, ce qui gomme les trous éventuels.
Il est important pour réduire la consommation que machin_id soit clef primaire.
L'inconvéniant de la méthode est que, telle quelle, elle prendra 3 articles qui se suivent. Il peut donc être intéressant de faire trois tirages consécutifs avec une limite à 1 enregistrement, mais c'est plus gourmand en requêtes.
Sinon, j'y pense, mais rien ne dit que ce soit plus rapide
récupérer les n° d'id par un "SELECT `id_post` FROM latable" et les stocker dans un tableau $liste_des_id,
puis
$id_post = array_rand($liste_des_id);
Et on a l'id voulu, garanti existant :)
Je ne garantis pas que ça ne déplace pas la surcharge sur php, c'est juste une autre façon de faire qui me venait - à tester...
(On peut éventuellement stocker cette liste des id quelque part et la mettre à jour à chaque nouvelle publication, pour éviter la première requête SQL.)
je viens de tester rapidement, sur une base contenant environ 2000 articles, j'obtiens ce genre de chiffres, en secondes d'exécution pour 10000 passages :
* RAND() MYSQL
18.3728899956
* la solution décrite dans ce post
1.62424921989
* ma solution
110.346151829 (ouch)
* ma solution, en mettant en "cache" la liste des n° d'id
1.26433396339
ça aurait donc le double avantage d'être plus rapide, et systématiquement juste, si je ne me suis pas trompé quelque part.
Disclaimer : attention quand même : j'utilise pour mes requêtes un genre de framework maison - ultra light mais sait-on jamais. Et je ne garantis pas l'exactitude de mon protocole, ni le fait que tu obtiendras les mêmes chiffres avec ta base de données... :)
hé bien ! Merci tout plein pour vos avis, je m'y penche juste après mon café ! :)
Bob ArdKor
bon en fait il est bien possible que j'aie merdé quelque part sur le test de la mise en cache, et que ma solution reste plus lente que la tienne - mais plus rapide que RAND()
enfin c'était une idée, comme ça
Bob ArdKor
Bon sinon il y a la solution façon gourou SQL :
jan.knesch...
et un ami me suggère un CRON nocturne pour éliminer les "trous", mais je ne sais pas si c'est possible sur cet hébergement.
bon courage !
Tsukenobi
Une question comme ca mais sur cette plateforme tu as la possibilité de faire tourner des crons ?, si oui tu peux reindexer les tables pour plus avoir de "trous" (bon avec les contraintes d'integrité ca peu etre un peu sioux, mais tu peux deja fair la cron pour creer ton tableau d'id et le mettre en cache comme Bob te le propose ) et donc ne plus faire q'un random php avec la bonne limite et donc n'avoir qu'une requete qui tombera tj sur un id existant ^^.
Sinon Bob a raison les select * c'est le MAL a eviter dans la mesure du possible.
Leloup
Re,
je viens de tester ma suggestion sur une table de 1189650 records, mais avec oracle comme sgbd. Vu qu'oracle ne supporte pas limit, j'ai pris un interval de valeur pour obtenir 10 enregistrements .
un select * prend 0,035 seconde
un select distinct * prend 0,016
un select champs1 a 5 prend 0,0078 seconde
le select count(*) from matable prend 0,066 seconde.
Une solution pourrait etre :
$y = select count(*) from matable
$x = rand(0,$y)
$tab = 'select champs1, [...] from matable limit ' .$x.',3'
Bob ArdKor
Après quelques tests supplémentaires, ma proposition n'est effectivement pas beaucoup plus rapide qu'un RAND() via SQL
La solution de Leloup est bien plus efficace, et ne renvoie pas d'id inexistants, mais elle n'est pas "très aléatoire".
Par exemple sur une base ne contenant comme numéros d'id que '1', '2', '3' et '10', on obtiendra beaucoup plus souvent l'id 10, puisque pour tous les tirages de 4 à 10, le résultat retourné sera 10.
Cf. le lien déjà donné plus haut, pour une solution à ce "problème"... mais évidemment on y perd en rapidité d'exécution.
Bon, c'est le week-end :)
Abyz
bonjour
$y = select count(*) from matable
$x = rand(0,$y)
$tab = 'select champs1, [...] from matable limit ' .$x.',3'
j'ai choisi c'est bien ça messieurs
merci pour ce topic
Autres trucs à lire :
– SuperShoes Sneakers by Mascaret
– Un sacré déconneur ! Préservatif " le pape a dit non "
– E-réputation pour tous Aliaz.com
– Le blog Silence Brisé par Missmanuella