Is RAND() function really bad?

Databases
1

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?

2

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.

3

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).

4

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!:wink:

5

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?

6

sorry… update regards time…

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

7

Please do not bump threads.

8

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!

9

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.

10

Hi,

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