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.