SitePoint Sponsor

User Tag List

Results 1 to 10 of 10
  1. #1
    SitePoint Addict
    Join Date
    Oct 2008
    Location
    Virtual World is my location
    Posts
    316
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

    Question Is RAND() function really bad?

    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?
    FreelanceNext.com - Freelance Projects / Jobs & more...
    BargainNext.com - coupons / deals / bargains / offers & more...

  2. #2
    Follow Me On Twitter: @djg gold trophysilver trophybronze trophy Dan Grossman's Avatar
    Join Date
    Aug 2000
    Location
    Philadephia, PA
    Posts
    20,580
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by jaagare View Post
    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.

  3. #3
    SitePoint Guru
    Join Date
    Jun 2006
    Posts
    638
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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. #4
    SitePoint Addict
    Join Date
    Oct 2008
    Location
    Virtual World is my location
    Posts
    316
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

    Question

    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!


    Quote Originally Posted by Vali View Post
    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).
    FreelanceNext.com - Freelance Projects / Jobs & more...
    BargainNext.com - coupons / deals / bargains / offers & more...

  5. #5
    SitePoint Addict
    Join Date
    Oct 2008
    Location
    Virtual World is my location
    Posts
    316
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

    Question

    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?
    FreelanceNext.com - Freelance Projects / Jobs & more...
    BargainNext.com - coupons / deals / bargains / offers & more...

  6. #6
    SitePoint Addict
    Join Date
    Oct 2008
    Location
    Virtual World is my location
    Posts
    316
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    sorry... update regards time...

    forgot to mention... that without rand() the time taken is 0.015 but with rand its still 5 seconds
    FreelanceNext.com - Freelance Projects / Jobs & more...
    BargainNext.com - coupons / deals / bargains / offers & more...

  7. #7
    Follow Me On Twitter: @djg gold trophysilver trophybronze trophy Dan Grossman's Avatar
    Join Date
    Aug 2000
    Location
    Philadephia, PA
    Posts
    20,580
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by jaagare
    d
    Please do not bump threads.

  8. #8
    SitePoint Addict
    Join Date
    Oct 2008
    Location
    Virtual World is my location
    Posts
    316
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Dan Grossman View Post
    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!
    FreelanceNext.com - Freelance Projects / Jobs & more...
    BargainNext.com - coupons / deals / bargains / offers & more...

  9. #9
    SitePoint Guru
    Join Date
    Jun 2006
    Posts
    638
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by jaagare View Post
    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.

  10. #10
    SitePoint Addict
    Join Date
    Oct 2008
    Location
    Virtual World is my location
    Posts
    316
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    Hi,

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

    Quote Originally Posted by Vali View Post
    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.
    FreelanceNext.com - Freelance Projects / Jobs & more...
    BargainNext.com - coupons / deals / bargains / offers & more...


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •