jaagare — 2010-02-12T09:52:12-05:00 — #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?
dan_grossman — 2010-02-12T10:42:09-05:00 — #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.
vali — 2010-02-12T11:47:52-05:00 — #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).
jaagare — 2010-02-12T22:48:35-05:00 — #4
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!;)
jaagare — 2010-02-13T00:16:45-05:00 — #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?
jaagare — 2010-02-13T01:39:34-05:00 — #6
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 — 2010-02-13T01:40:15-05:00 — #7
Please do not bump threads.
jaagare — 2010-02-13T03:47:02-05:00 — #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!
vali — 2010-02-16T15:55:53-05:00 — #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.
jaagare — 2010-02-17T07:25:47-05:00 — #10
Thanks for your help and the link. Will check it out...