I have a db of about 300,000 records and when I try to find one random record like this:
select * from table order by rand() limit 1;
it can take several minutes. My Sherlock Holmes instincts tell me that what I'm doing is somehow inefficient. What is the primary culprit here?
The culprit is that mysql has to assign a random value (via rand()) to each of the 300,000 rows, then order all 300,000 rows by that random value and return the first one.
So even though you're just retrieving one value, you're processing 300,000 rows.
You'd be better off doing something like this in your application..
row_count = get row count; random_row = get random number from 0 to row_count - 1; result = db query LIMIT 1 OFFSET random_row
or something like that...
-philip
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
