Is RAND() function really bad?

[jaagare]

I used RAND() function and don't seem to notice much difference. My table size is around 10000 records and on my local system it takes around 5 seconds when I run RAND() LIMIT 50

Secondly using the RAND function many times I get either the top most random records or lower side.

Is there any better way I could get 50 random records spread across the entire table?

[Dan Grossman]

Is there any better way I could get 50 random records spread across the entire table?

A more even distribution would be the opposite of random. Clumping is far more statistically likely than an even distribution when choosing elements in random order.

[Vali]

5 sec for 10000 records is REALLY slow... add some indexes to it.

BUT, a way faster solution would be this (assuming your not deleting to many records):
- have your table primary key be numeric, auto increment.
- with PHP, generate ~100 random numbers, from 0 to your max(id)
- SELECT * FROM table WHERE id IN ('.join(',',$ids).') LIMIT 50;
- Repeat the select until you get 50 records returned.

This can make your 5 sec select to 0.0005 sec, as long as you select from the entire table (works in most situations).

[jaagare]

Hi,

The issue with my table is that the numbers are not in a series... its like 1,2 then say 50, then 80,81,82 then 100... then again 101, 102,........10000

its because records in between have been deleted. So if I use the query any way I could find if that number is available in the table? and then run the select query?

An example would be highly appreciated cos I am not really very good with getting items into arrays and make mistakes most of the time!

5 sec for 10000 records is REALLY slow... add some indexes to it.

BUT, a way faster solution would be this (assuming your not deleting to many records):
- have your table primary key be numeric, auto increment.
- with PHP, generate ~100 random numbers, from 0 to your max(id)
- SELECT * FROM table WHERE id IN ('.join(',',$ids).') LIMIT 50;
- Repeat the select until you get 50 records returned.

This can make your 5 sec select to 0.0005 sec, as long as you select from the entire table (works in most situations).

[jaagare]

Update with regards time...

I had placed the time counter at the wrong place. It was showing me 5 seconds for generating entire page... but the rand query takes around 0.015 ...so would any other query be better or is rand the best in my case?

[jaagare]

sorry... update regards time...

forgot to mention... that without rand() the time taken is 0.015 but with rand its still 5 seconds

[Dan Grossman]

d

Please do not bump threads.

[jaagare]

Please do not bump threads.

Hi.. sorry for that... dont know how only that d came up instead of my entire post! fixed it by editing immediately... i think u noticed it as soon as I posted!

[Vali]

sorry... update regards time...

forgot to mention... that without rand() the time taken is 0.015 but with rand its still 5 seconds

The simplest solution for you is to add some correct indexes.

A BETTER solution for you is something like this: http://jan.kneschke.de/projects/mysql/order-by-rand/

He explains better what I tried to explain, and takes it a bit further.

[jaagare]

Hi,

Thanks for your help and the link. Will check it out...

The simplest solution for you is to add some correct indexes.

A BETTER solution for you is something like this: http://jan.kneschke.de/projects/mysql/order-by-rand/

He explains better what I tried to explain, and takes it a bit further.