alternatives to RAND() ?

[sitNsmile]

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

[Old Pedant]

?? 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.

[sitNsmile]

Originally Posted by Old Pedant

?? 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.

[Old Pedant]

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:

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.