Discussion :

[Oulalahakabu]

Bonjour,

Grosse colle, j'ai besoin d'effectuer un tirage aléatoire sur un base de donnée MySQL en php (symfony2).
La colonne identifiant est de type BIGINT unsigned, pour faire cela, je fais un truc du genre (simplifié) :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
 
$count = 'select max(id) from table';
$random = rand(0, $count);
$result = 'select * from table where id>=$random';

Ca fonctionne très bien sauf que j'aimerai que l'espérance mathématique d'être sélectionné soit strictement la même pour chaque tuple.

Je ne parle pas du rand() que j'ai mis de cette manière pour illustrer mon procédé, mais de la possibilité que certains tuples soient supprimés de la base : si les tuple d' ID 5 et 6 sont supprimés, le 7 aura ainsi plus de chance d'être tiré que le 4, et c'est ce qui me gène !

Quelle est la meilleur façon de procéder pour ce type de sélection ? (un simple ORDER BY RAND() n'est pas une solution envisageable sur cette table (+300000 entrées).

[Nowwis]

Salut,

Solution un :

Une méthode pourrait être de stocker dans un tableau tous tes id

(je suppose que tu comprendras !)

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
SELECT id from table
$data = query();

Puis de faire un rand(0, count($data));

Et comme ça tu récupéres un id existant à chaque fois.



Solution deux, plus gourmande.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
$count = 'select max(id) from table';
do
{
$random = rand(0, $count);
$reponse = mysql_num_row(mysql_query('select * from table where id=$random');
}while($reponse != 1);

Ton random te donnera ton id gagant.

[ericd69]

salut,

tu peux le faire plus simplement avec order by rand()

Code sql :

Sélectionner tout - Visualiser dans une fenêtre à part

select * from table where 1 order by rand()

tu peux préciser un seed entier dans rand si besoin pour ne pas risquer de régénérer une séquence similaire...

[Oulalahakabu]

@ericd69 : Mon problème n'est pas nécessairement au niveau du SQL, cette requête est LA requête de cette application (imaginons 100000 utilisateurs effectuant cette requête en moyenne toutes les 5 secondes). Je n'ai pas réellement de souci pour obtenir un tuple de manière aléatoire avec la même espérance, je cherche à obtenir le résultat d'une telle requête de manière rapide : mon souci se situe au niveau de la perf. Le ORDER BY rand() est donc à bannir de ce point de vue.

@Nowwis : ta solution 2 est justement celle qui est en production, c'est la plus élégante que j'ai à l'heure actuelle ( à toi, mais je cherche à faire mieux, je suis sur que cela doit exister, en tout cas je l'espère )

[ericd69]

tu peux éviter les aller retour php/mysql en faisant une procédure stockée qui fait ces calculs cotés mysql... déjà 1 requête au lieu de 2 à envoyer et interpréter..

ça va très légèrement optimiser surtout si tu as beaucoup de requêtes en parallèle...

tu peux optimiser le traitement en ne jouant que sur id pour classer avec rand() comme ça tu utilise l'index de clé primaire... et tu n'affiches que les lignes ensuite...

si tu mets un * il n'y aura jamais d'optimisiation...

et si tu ne veux que par exemple les n premières id c'est d'autan plus efficace...

Code sql :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
select *
from table 
where id in
    (select id from table 
     where 1 
     order by rand()
     limit 10)

si les performances ne sont pas top ça peut venir d'un bug sur les sous-requête) de l'optimiseur que tu peux voir en faisant un explain...
il est facilement contournable en stockant le résultat de la sous requête dans une table temporaire et d'utiliser cette dernière dans le in de la requête principale...

à tester...

en tout cas, si tu as beaucoup de connexion parallèle il faut minimiser le nombre d'échange entre php et mysql vu que la plupart du temps c'est fait en tcp/ip...

PS: mathématiquement votre méthode à tous les 2 favorise les tuples du milieu et élimine statistiquement un certain nombre parmi les premiers...

[rawsrc]

Bonjour,

Sur des grandes tables, les performances s'effondrent avec un ORDER BY RAND(), il faut fuir cette solution comme la peste.
Le mieux c'est de faire une auto-jointure avec une table générant les aléatoires.
Quelque chose comme ça :

Code sql :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
SELECT * 
FROM 
   t_table
      INNER JOIN (
         SELECT FLOOR(MAX(t_table.id)*RAND()) AS max_id 
         FROM t_table
      ) AS t_alea ON t_table.id >= t_alea.max_id 
LIMIT 10