Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 4 of 4
  1. #1
    Regular Coder sitNsmile's Avatar
    Join Date
    Dec 2009
    Location
    Charlotte, NC
    Posts
    358
    Thanks
    19
    Thanked 2 Times in 2 Posts

    alternatives to RAND() ?

    So I have hit a problem with my database, where RAND() causes too many temporary tables. Is there another better performance way to run "RAND()" with a query?

    Thanks

  • #2
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    26,189
    Thanks
    80
    Thanked 4,453 Times in 4,418 Posts
    ?? Why would RAND() create any temporary tables, at all??

    But yes, RAND() is generally a bad solution for any table query with more than, say, a couple of thousand records.

    But a lot depends on how many records you need to SELECT using RAND() or equivalent. If you only want 1, there are several tricks you can pull, especially if you don't need perfection in the randomization but just need something that feels random.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #3
    Regular Coder sitNsmile's Avatar
    Join Date
    Dec 2009
    Location
    Charlotte, NC
    Posts
    358
    Thanks
    19
    Thanked 2 Times in 2 Posts
    Quote Originally Posted by Old Pedant View Post
    ?? Why would RAND() create any temporary tables, at all??

    But yes, RAND() is generally a bad solution for any table query with more than, say, a couple of thousand records.

    But a lot depends on how many records you need to SELECT using RAND() or equivalent. If you only want 1, there are several tricks you can pull, especially if you don't need perfection in the randomization but just need something that feels random.
    Okay. and when you mean a couple thousand results, such as if i select from status = 1 (that returns 500) and not using status would return say 10,000, RAND() would best work with status = 1 (randomizing 500) because it wont try to randomize 10k. (as an example. I think thats correct).

    The temp table thing, was something my hosting provider alerted me on about the query creating temp tables. If it creates a problem randomizing like say 10,000 results, what would be the best way to do a random alternatively with the same "random" effect. technically I could throw them in a array and using a shuffle, but I hate to grab that much resource just to limit it to 1 result.

  • #4
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    26,189
    Thanks
    80
    Thanked 4,453 Times in 4,418 Posts
    Well, the easiest: If you have a primary key, especially one that is auto_increment, then just pick a random number from 1 to MAX(PK) and then go get that one record. If you have any "holes" in the table (e.g., from deleted records), then just do this:
    Code:
    SELECT * FROM table WHERE PK >= (SELECT 1 + FLOOR( RAND() * MAX(PK) ) FROM table ) LIMIT 1
    The more holes you have in the PK numbering, the less random the results will be.

    Say you had PK values of 1,2,11,12. Since the numbers from 1 to 12 will be picked roughly equally by the random expression, that means that 11 will be the final result when the random choice is any value from 3 through 11. So 11 will occur 75% of the time as the choice! But if your "holes" are themselves reasonably randomly occurring within the full range of values, then the results will be reasonably random as well.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.


  •  

    Posting Permissions

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