Select a random row from large mysql table in an optimized way.

Posted on | March 10, 2008 |

Random — This simple pronounced and apparently gentle looking word can make your brain randomly scattered.

We often need to select a random row from a table. … cutting short.. less time.

Prob: Need a random row from a table name ‘Dim’ which has 1,000,000 rows.

{selected only pad, i am in hurry :(}

Create a stored procedure:

create procedure getDimRandom()
begin
declare cnt INT default 0;
DECLARE cur1 CURSOR FOR SELECT count(*) FROM dim;
open cur1;
fetch cur1 into cnt;

select pad from dim where pad=round(rand()*cnt);
end

Time: 1.32 sec max in 10 try

Prob: I like it

2. select floor(rand()*99999), pad from dim where pad = floor(0 + rand()*99999) limit 0,1;

Prob: here i used 99999 hard coded. can be passed from script page like php, perl.

Time: 4.32 sec max in 10 try

3. select pad, rand() as rnd from dim order by rnd limit 1

or select pad from dim order by rand() limit 1.

Time: 4.56 sec max in 10 try

horrible way:

bye for now.. check back later.

Category: mysql

4 Responses to “Select a random row from large mysql table in an optimized way.”

Terrydt
March 24th, 2008 @ 12:21 am

well done, man
mamamia10
April 3rd, 2008 @ 2:07 am

To be sincere, I have never been here before, and as a new person I can say that this website looks amazing. I really enjoy it
Robert
April 6th, 2008 @ 8:01 am

stop saying grose things about this! It’s your terrible way of thinking! Normal people don’t care about stuff like that
Bitcloud
April 9th, 2008 @ 2:31 am

Your previous posts were real rubbish, but this is good. This one is brilliant. Your blog is getting really better.

Shaikh Sonny Aman’s Blog