Select Random from 3 Million rows

Question

I have 3.2 million rows with a few keys to help speed up the sorting. However, as my table grows, things are getting slow and slower each day. Can you guys please take a look at my query and let me know if there are any other solutions I can have to make this process quicker better?

SELECT * FROM (SELECT `numbers` FROM `avotf`.`master` WHERE `active`=1 order by `monthly_mins`,`called`,`added`,rand() limit 200) AS T1 ORDER BY RAND() LIMIT 1

possible duplicate of [MySQL select 10 random rows from 600K rows fast](http://stackoverflow.com/q/4329396/1409082) and [Getting random results from large tables](http://stackoverflow.com/q/12356733/1409082) — Jocelyn, Apr 27 '13 at 12:58

score 3 · Answer 1 · answered Apr 26 '13 at 14:25

Assuming that your table has a numeric id column, something like this should work (borrowed from here):

SELECT * FROM `table` 
 WHERE id >= (SELECT FLOOR( MAX(id) * RAND()) FROM `table` ) ORDER BY id LIMIT 1;

Do not do this:

SELECT * FROM `table` ORDER BY RAND() LIMIT 1;

It works but it will be extremely slow

score 0 · Answer 2 · answered Apr 26 '13 at 14:26

0

The problem is because you use RAND() with lots of data

You should read the following blog post of Jan Kneschke: ORDER BY RAND()

He lists a few possible solutions and their performance behaviour.

answered Apr 26 '13 at 14:26

TheEwook

11,037
6
36
55

score 0 · Answer 3 · answered Apr 26 '13 at 14:27

It's well known that ORDER BY RAND() can be very slow, and while you've tried to select against a subselect here, that still creates a temporary table.

It's not clear why you select RAND() in the subselect, then ignore this and create a new sort key on the outer select. Maybe you could make use of that.

Your ORDER BY conditions are quite extensive, and if you're missing an index there then it'll be row-scans to get them all.

Don't forget to EXPLAIN your query.

Select Random from 3 Million rows

3 Answers3