Is there a way to be less random than ORDER BY RAND to increase speed?

Question

I run this query 5 times, 5 seconds apart, on a table of 500,000 rows:

SELECT * FROM `apps` WHERE dev_name = '' ORDER BY RAND() LIMIT 10;

I'd like to get 50 rows that have a 90-95% chance of being unique. The query takes 10 seconds right now. I'd rather get it lower and have a smaller chance of being random.

score 0 · Answer 1 · answered Dec 16 '15 at 17:43

0

Try

AND RAND() >= 0.90

(or 0.95 if you like) in your WHERE clause.

answered Dec 16 '15 at 17:43

user207421

305,947
44
307
483

That'll be faster than `ORDER BY RAND`? – User Dec 16 '15 at 18:07
Yes, but not very random at all. The chance of getting an entry back that comes late in the natural order of the table is very small that way. – luksch Dec 16 '15 at 18:31
@luksch Why? It should select any row with the same probability. – user207421 Dec 16 '15 at 23:14

score 0 · Answer 2 · edited May 23 '17 at 12:15

0

You may want to study Fetching Random Rows from a Table, as highlighted in here in a related question. Another great article can be found here.

edited May 23 '17 at 12:15

Community

1
1

answered Dec 16 '15 at 18:46

luksch

11,497
6
38
53

Is there a way to be less random than ORDER BY RAND to increase speed?

2 Answers2