May/084
ORDER BY RAND() Optimisation
Il n’existe pas d’autres moyen magique de sélectionner des enregistrements aléatoirement. Il y a seulement ORDER BY RAND(). Cependant, ORDER BY RAND() est un performance killer lorsque la table possède plusieurs milliés d’enregistrements.Prenons le cas où on veut afficher 10 enregistrements aléatoirement et que cette requête va être exécutée excessivement souvent par l’application. La table utilisée pour les tests est une table avec 500000 enregistrements.
Pour commencer, voyons voir comment MySQL réagis avec une requête qui ordonne sur la clef primaire avec une limite de 10 enregistrements:
-
SELECT SQL_NO_CACHE *
-
FROM tableA
-
ORDER BY id LIMIT 10;
-
// 0.00 sec
On s’y attendait, c’est tellement rapide que le client Mysql n’arrive pas à être assez précis pour dire le temps que ca a pris. Maintenant, une requête qui ordonne par RAND() avec une limite de 10 enregistrements également:
-
SELECT SQL_NO_CACHE *
-
FROM tableA
-
ORDER BY RAND() LIMIT 10;
-
// 2.24 sec
La différence est énorme considérant qu’on passe de plus petit que 0.00 à 2.24 secondes. Puisque ORDER BY RAND() est la seule manière de sortir des enregistrements aléatoirement, on peut croire qu’on doit vivre avec. C’est presque vrai..!
Si la logique de l’application le permet, il est possible de restraindre le range des colones aléatoires. Il faut cependant comprendre qu’on limite ainsi le champs de valeur possible. On peut le faire de deux manières:
-
SELECT SQL_NO_CACHE *
-
FROM (SELECT * FROM tableA LIMIT 100000) rs
-
ORDER BY RAND() LIMIT 10;
-
// 0.38 sec
-
SELECT SQL_NO_CACHE *
-
FROM (SELECT * FROM tableA WHERE id > 1 AND id < 250000) rs
-
ORDER BY RAND() LIMIT 10;
-
// 1.21 sec
Ce n’est pas un random absolue, mais je selectionne quand même 10 enregistrements aléoirement dans un range de 0 à 100000 dans le premier cas, et dans un range de 1 à 250000 (50% des enregistrements) dans la seconde requête.
L’avantage de la deuxième syntaxe permet à l’application de générer elle-même un range aléatoire. On peut établir une regle selon laquelle le range ne doit pas exceder 50% des valeurs. On peut donc avoir un code PHP simpliste comme:
-
$min = rand(1,250000);
-
$max = rand(250000,500000);
-
// s'assurer que le range soit suffisament grand et pas trop petit ..
-
$sql = "SELECT SQL_NO_CACHE *
-
FROM ( SELECT * FROM tableA
-
WHERE id > ".$min." AND id < ".$max."
-
) rs
-
ORDER BY RAND() LIMIT 10;
-
Et voila! Chaque fois que l’application exécute cette requête, les valeurs aléatoires auront l’aire réelement aléatoires beaucoup plus rapidement 
Enjoy this article?
Leave a comment
No trackbacks yet.
2:26 pm on August 15th, 2009
Yup sure!!! Sa vas me servir merci!!!
4:04 am on October 14th, 2009
Ca fonction quand on connait à l’avance la taille de la table. Mais si on ne la connait pas?
9:08 pm on October 14th, 2009
Si c’est une table MyISAM, il peut quand même s’averer plus rapide en allant chercher le nombre total d’enregistrements puisqu’un SELECT count() est instantané.
Parcontre si c’est InnoDB, ca reste à voir…! Le mieux est d’essayer les différentes possibilités!
2:15 pm on January 6th, 2010
Il manque une guillement à la fin de ton affectation mais c’est un détail.
Autrement ta méthode est bonne même si je l’aurai appliquée différemment :
Dans le pire des cas tu auras $min = 1 et $max = 500000, et retombera sur le même temps d’exécution, voir un plus important.
Une autre application selon la suivante :
$min = rand(1,250000);
$sql = ‘SELECT SQL_NO_CACHE *
FROM (SELECT * FROM tableA LIMIT ‘.$min.’, 250000) rs
ORDER BY RAND() LIMIT 10′;
L’approche n’est plus tout à fait la même, le résultat non plus, mais ça te donnera un temps constant et tu travailleras toujours sur tout ton intervalle non ?