Optimiser un rand() MySQL en PHP

Photo : 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 !

Car voilà, mon gentil hébergeur me le fait savoir par mail, c'est soit je m'en vais en dédié ailleurs que chez eux, soit j'optimise. Et ce qui fait mal sur les sites du domaine tribords.com, ce sont les bouts de scripts qui affichent aléatoirement. Plus une base grossit, plus la fonction rand() de MySQL est longue à traiter. Il faut donc impérativement éliminer ces randoms, sinon c'est le blâme et le bonnet d'âne. Bref, voilà comment j'ai fait !

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 :)
Maintenant, à vous de jouer :
Pinterest
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.

Twitter Facebook LinkedIn

Blogueur ? Auteur ? Rejoignez la rédaction !


Et aussi ...

#InDevWeTrust

Devenez développeur Web avec O'clock !
Si vous avez toujours rêvé de toucher votre bille en code, de murmurer à l'oreille des écrans ou de dompter des cascades très stylées, l'école O'clock va sûrement vous plaire ! Parmi les nombreuses formations web qui pullulent désormais sur le marché, celles de développeur web...

Améliorer son site web
C'est toujours un plaisir de faire plaisir en améliorant un site, surtout quand on peut se payer à l'occasion un titre bien marketeux ;) Pas mal de changements pour le blog avec des développements pour améliorer votre confort de visite ... vous êtes gâtés ! :) Premier point : vous pouvez désormais voir le nombre de commentaires dans un billet en mode liste comme...

Ajoutez votre avis !

14 avis lumineux

Mr Xhark 

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 :)

Simon 

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 ...

Bob ArdKor 

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 ;)

Benoit 

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.

Bob ArdKor 

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.)

Bob ArdKor 

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... :)

Simon 

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 :

– Images de notre société ... Dispositifs anti-SDF
– Home geek home Juste-une-Maison.com
– épistolaire ! Lettres d'un Inconnu
– Le blog Le jardin le Clos fleuri dans la Drome par Claudette Fumat

NEWSLETTER FOR EVER !

©2006-2018 Tribords.com le blog lifestyle de Simon Tripnaux Content Manager, créateur du hashtag #JeudiPhoto et soutien de #CotedAzurFrance

Consultant SEO à Nice - Cannes - Monaco - 06 56 84 42 53