comment optimiser "order by rand()"

+4 votes
8,216 vues
question 4 Décembre 16 dans Sql Phpmyadmin par paco (436 points)  
J'utilise Wordpress avec un module de news professionnel.

Ce dernier tire au hasard 8 news parmi 400.000 en utilisant order by rand(). Le résultat met 12 secondes pour être calculé selon les logs de mysql_log_slow

j'ai mis partout des index et pourtant mysql scrute toute la table.

Y aurait il un truc pour éviter cette lenteur ?
commentée 4 Décembre 16 par ladmin (2,277 points)  
Oui, il y a des "trucs" mais qui demandent un peu d'organisation. Voyons si quelqu'un répond sinon j'y reviendrai

1 Réponse

+3 votes
réponse 4 Décembre 16 par ladmin (2,277 points)  

Si vous voulez effectuer un vrai order by rand(), en supposant que cela ait un sens particulier, il n'y a rien à faire , ce sera lent car vous demandez au serveur de prendre une valeur aléatoire pour chaque ligne, de tout classer pour en retenir quelques lignes, déterminées par le limit.

Dans la pratique, c'est la mauvaise méthode utilisée par pas mal de modules et programmes pour tirer 8 news au hasard parmi un gros paquet.

Je vous suggère cette méthode

- ajoutez une colonne appelée ordre_variable type int, faites en un index simple

ALTER TABLE `la_table_de_la_requete`  ADD `ordre_variable` INT NOT NULL DEFAULT '0' AFTER `bbb`, ADD INDEX (`ordre_variable`);

- exécutez une première fois et ensuite par cron tous les jours ou toutes les semaines:

UPDATE la_table_de_la_requete set ordre_variable = int(RAND()*1000000) ;

- transformez la requête

select ... where ... order by rand() limit 0,8 ;

en

set @a=800000*rand() ;

select ... where ... AND ordre_variable > @a order by ordre_variable limit 0,8 ;

Avec 400.000 lignes, on peut être sûr d'avoir 8 lignes qui satisfont la nouvelle condition. Le Rand() reste efficace même si entre 2 passages en Cron , les séquences sont fixes dès qu'on a déterminé la position de la première ligne sélectionnée. Mais l'effet recherché est bien là.

Pour installer le cron, installez le module Cron de votre Cms ou bien contactez votre SAV pour une solution avec crontab sur votre serveur web.

 

commentée 5 Janvier 17 par anvers (269 points)  
impeccable. On peut ajouter un autre facteur de random en mettant du coup un vrai sort avec un tirage aléatoire de la colonne ou de la combinaison de colonnes du sort dans le php préparatoire
commentée 5 Janvier 17 par ladmin (2,277 points)  
voulez vous dire en plus du sort sur la colonne ordre_variable ??? ça ne marcherait pas toujours, en particulier si les valeurs d'ordre_variable sont très diverses avec peu de doublons. Faites plutôt en php un sort aléatoire du résultat
commentée 5 Janvier 17 par anvers (269 points)  
en effet , il faut faire le sort aléatoire de colonne après le sort sur ordre_variable, en php.
commentée 5 Janvier 17 par ladmin (2,277 points)  
le lecteur qui n'a pas compris pourra toujours demander des précisions :)
Feel free to ask and answer in english

Bienvenue sur les FAQ de 123.fr, posez vos questions ou répondez à celles des autres usagers.

...