Help tuning a very poor SQL query

[eluser]TheFuzzy0ne[/eluser]
Hi, Leon. I just wanted to follow up on my previous suggestion, and post back the results of my test. I had a million rows in my database:

Counting the rows, selecting a random number, then grabbing a row took 0.0787 seconds:

Code:

SELECT * FROM `tmp_table` LIMIT 90016, 1;

Ordering by RAND() took an incredible 81.3853 seconds!

Code:

SELECT * FROM `tmp_table` ORDER BY RAND() LIMIT 1;

Ordering by RAND() is OK for smaller tables, but it doesn't scale well. I wish MySQL would implement a change that picked up on when the results were to be ordered by rand, and instead count the rows, select a random number and return a row that way instead.

All the best.

[eluser]Dam1an[/eluser]
Wow thats a big difference!!!

When using a random number between 0 and row count, you need to take into account that row may not exist if you delete rows

[eluser]TheFuzzy0ne[/eluser]
That's true, but the likelihood of rows being deleted within that small time frame is quite unlikely, and even still, so long as the offset isn't too close to the end of the table, it shouldn't pose any problems. It should be simple enough to do a loop to account for that if you don't get a row back.

[eluser]Leon Stafford[/eluser]
Thanks guys,

I'd like to put the optimized random code in now...

The examples above I think are for a single random row, how about for say 20 random rows (no duplicates)?

[eluser]TheFuzzy0ne[/eluser]
Any reason why you need 20 random rows? That could be a serious performance issue. If you can give more details on what you need to do, there may be a better solution. What's the maximum number of rows you expect to have in your table?