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?
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.
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).
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!;)
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?
sorry... update regards time...
forgot to mention... that without rand() the time taken is 0.015 but with rand its still 5 seconds
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!
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.
Thanks for your help and the link. Will check it out...