Fast Random Results - "ORDER BY RAND()" is bad - Page 2
Page 2 of 2 FirstFirst 12
Results 16 to 28 of 28

Thread: Fast Random Results - "ORDER BY RAND()" is bad

  1. #16
    Senior Member Roger Ramjet's Avatar
    Join Date
    Jul 2004
    Location
    Leeds, UK
    Posts
    4,203
    Cool, Sxooter, go for it. I'm back down there myself in a mo to watch Chelsea v Blackburn in the FA cup semis.

  2. #17
    Pna lbh ernq guvf¿
    Join Date
    Jul 2004
    Location
    Kansas City area
    Posts
    19,429
    Thread stickied; if any cleaning up needs to be done, Sxooter, let me know.

  3. #18
    w00t Bozebo's Avatar
    Join Date
    Oct 2007
    Location
    Aberdeen, Scotland
    Posts
    26

    resolved

    Wow that's a sweet tip... I used to do it that way about 3 years ago when I was starting out and had no idea there was an ORDER BY rand() possibility.

  4. #19
    Senior Member
    Join Date
    Jun 2005
    Location
    UK
    Posts
    102
    Quote Originally Posted by Roger Ramjet
    We see so many folk trying to use this RAND() thing for good reasons on their websites because they want to display random advertising banners
    I'm stuck on this one now, was going to use RAND() to pick 1 of 3 adverts, but decided to rotate them...

    Quote Originally Posted by Roger Ramjet
    The solution to displaying random events to individual users is already taken care of by their random page hits. All you need to do is to cycle sequentially through your items and that is a simple algorithm with easy optimisation.
    Can you share this solution/algorithm with us? Please
    To touch is one thing, to feel is another.

  5. #20
    PHP Witch laserlight's Avatar
    Join Date
    Apr 2003
    Location
    Singapore
    Posts
    13,915
    I'm stuck on this one now, was going to use RAND() to pick 1 of 3 adverts, but decided to rotate them...
    Rotation should work just as well.

    Can you share this solution/algorithm with us?
    Basically, keep some kind of pointer to the current item. Increment (and loop around) the pointer whenever the current item is displayed. In other words, Roger Ramjet suggested your rotation solution.
    Use Bazaar for your version control system
    Read the PHP Spellbook
    Learn How To Ask Questions The Smart Way

  6. #21
    Senior Member
    Join Date
    Jun 2005
    Location
    UK
    Posts
    102
    Quote Originally Posted by laserlight
    Basically, keep some kind of pointer to the current item. Increment (and loop around) the pointer whenever the current item is displayed. In other words, Roger Ramjet suggested your rotation solution.
    Hi

    In the deep dark night I managed it, of course it was very simple, but me being a man with Man Flu at the time I just could not get my head around it

    The answer in case anyone is looking for it is simply have a counter, when the ad is shown add 1 to that counter, the query is simply

    Code:
    SELECT * FROM adverts ORDER BY counter_total LIMIT 1
    It only kicks in and works of course [ and this is where I blame the man flu ] if the counter_total filed is set to int()
    To touch is one thing, to feel is another.

  7. #22
    Junior Member
    Join Date
    May 2008
    Posts
    9
    Which solution you think is best to use as fastest method:
    Method 1:


    $db->query("SELECT id, title, date, alt_name, short_story, rating, news_read FROM " . PREFIX . "_post WHERE approve='$conf[allow_index_metka]' AND category regexp '[[:<:]]($conf[cat_num_1])[[:>:]]' AND id >= (SELECT MIN(id) + ROUND( ( MAX(id)-MIN(id) ) * RAND() ) FROM " . PREFIX . "_post ) ORDER BY id LIMIT 4");


    Method 2:


    $db->query("SELECT id, title, date, alt_name, short_story, rating, news_read FROM " . PREFIX . "_post WHERE approve='$conf[allow_index_metka]' AND category regexp '[[:<:]]($conf[cat_num_1])[[:>:]]' AND id >= (SELECT FLOOR( MAX(id) * RAND()) FROM " . PREFIX . "_post ) ORDER BY id LIMIT 4");



    or to use something else perhaps?

  8. #23
    *AMPed CoderDan's Avatar
    Join Date
    May 2009
    Location
    Dallas, TX
    Posts
    338
    Quote Originally Posted by djalecc View Post
    The answer in case anyone is looking for it is simply have a counter, when the ad is shown add 1 to that counter, the query is simply

    Code:
    SELECT * FROM adverts ORDER BY counter_total LIMIT 1

    This solution is usually fine and has the added bonus of counting views.
    However, there are times when you have a pool of data you want to rotate that may change - elements added or removed.
    In that case, I use a datetime field, which is updated to current time.
    Of course, my stuff runs usually on a batch, not on a user request, so this is perfect.
    If the request is expected to be run on a daily basis, just use date, if it's going to be run on something less than a day, and larger than a second, just use time.
    If it's going to be run more than once a second, you might want to look at using an int field again, and storing a microtime component from php or similar

  9. #24
    Chamberlain Sxooter's Avatar
    Join Date
    Aug 2002
    Location
    Denver, CO
    Posts
    4,027
    That's a good way to do it. I think in Postgres (and maybe later model MySQL) I'd setup a trigger that used a sequence (autoinc standalone thingie in pgsql, you could use a locked table in mysql with a single field that increments to simulate the same thing) to update the field everytime the row was updated / accessed / checked out or whatever. You could also use a function or stored proc to do it in either db too.

    The advantage to using a sequence or something like it is that it makes it impossible to have a collision.
    PostgreSQL, because your data matters.

  10. #25
    Senior Member Roger Ramjet's Avatar
    Join Date
    Jul 2004
    Location
    Leeds, UK
    Posts
    4,203
    Quote Originally Posted by gtkatz View Post
    Which solution you think is best to use as fastest method:
    Method 1:


    $db->query("SELECT id, title, date, alt_name, short_story, rating, news_read FROM " . PREFIX . "_post WHERE approve='$conf[allow_index_metka]' AND category regexp '[[:<:]]($conf[cat_num_1])[[:>:]]' AND id >= (SELECT MIN(id) + ROUND( ( MAX(id)-MIN(id) ) * RAND() ) FROM " . PREFIX . "_post ) ORDER BY id LIMIT 4");


    Method 2:


    $db->query("SELECT id, title, date, alt_name, short_story, rating, news_read FROM " . PREFIX . "_post WHERE approve='$conf[allow_index_metka]' AND category regexp '[[:<:]]($conf[cat_num_1])[[:>:]]' AND id >= (SELECT FLOOR( MAX(id) * RAND()) FROM " . PREFIX . "_post ) ORDER BY id LIMIT 4");



    or to use something else perhaps?
    That is pretty much it really and only benchmarking will really tell which is faster.

    Now there is an associated problem of displaying a random result from a sub-table for each record in a main table which I have discussed in This Thread. It uses the same approach more or less, except that I suggest using a trigger to maintain sequnce numbers and counts on the basis that inserts are far less frequent than reads in almost every application.

  11. #26
    Junior Member
    Join Date
    Oct 2010
    Posts
    1
    Quote Originally Posted by NogDog View Post
    Just a thought I had, no idea if it would perform any better:

    1. Do a SELECT COUNT(*) to get the number of records

    2. Do a <?php $offset = rand(0, $count - 1); ?> where $count comes from the above query.

    3. Do a SELECT * FROM table LIMIT 1 OFFSET $offset

    I have no empirical evidence how this would perform versus the ORDER BY RAND() method, but figured I'd throw it out there in case you want to try it.
    Thanks to laserlight's point, I ran the same query to populate the summary table, but with the last month's data instead of the last 48 hours. It took quite a while to build the summary table, 450 seconds. And it inserted 6787330 rows. Select time still hovers around 50 milliseconds for the first time on a row, 1 millisecond the second time when it's block is in memory.

  12. #27
    Junior Member
    Join Date
    Feb 2004
    Location
    Mumbai
    Posts
    5
    You can user select fields1, fields2, fieldsN from table and user LIMIT and index.
    it will help you to increase speed of database.

  13. #28
    PHP Witch laserlight's Avatar
    Join Date
    Apr 2003
    Location
    Singapore
    Posts
    13,915
    Quote Originally Posted by kamlesh
    You can user select fields1, fields2, fieldsN from table and user LIMIT and index.
    it will help you to increase speed of database.
    I think you're missing the point of this thread. If not, I suggest that you explain further in the context of "fast random results" when selecting from a database table.
    Use Bazaar for your version control system
    Read the PHP Spellbook
    Learn How To Ask Questions The Smart Way

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

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