Selecting a random row from a huge database?

[FoxhoundX]

Alright, I'd like to select a random record from a huge database (As in, nearly 100,000,000 records).

My normal code is this:

$query="SELECT * FROM testtable";
$result=mysql_query($query);
$num=mysql_numrows($result);
echo $num;

Then using that resulting number, get a random number (rand(1,$num), and select using that, but the above code takes more than 30 seconds to execute. I'd rather it be quick (So people don't have to wait an hour for the page to load. 😛)

[mrhappiness]

SELECT * FROM testtable ORDER BY RAND() LIMIT 1

[FoxhoundX]

I have also tried that;

$result = mysql_query("SELECT * FROM `testtable` WHERE `v`='0' ORDER BY RAND() LIMIT 1");
while ($row = mysql_fetch_array($result,MYSQL_ASSOC)) { 
$test = $row{'num'};
}
echo $test;

?>

Again, it times out.

Are there any settings I could change within phpMyAdmin like indexing that would make it faster? :\

[________]

Take a look at the explain for "select * from table order by rand() LIMIT 1"

Extra: Using temporary; Using filesort

A query like that would probably timeout with about 50 000 to 100 000 rows.

It would be much faster to run 2 queries:
1. count the rows
then use eg. php to select a random number bigger then 0 but smaller then the number of rows.
2. select * from table where id = the randon number.

Just make sure id is the primary key, or an index.

[tuf-web_co_uk]

shouldnt the code be

<?
$result = mysql_query("SELECT * FROM `testtable` WHERE `v`='0' ORDER BY RAND() LIMIT 0,1"); 
while ($row = mysql_fetch_array($result,MYSQL_ASSOC)) {  

$test = $row{'num'}; 
} 
echo $test; 

?>

notice that i have changed

LIMIT 1

to

LIMIT 0,1

the first value in the limit decides where you want to start showing the results from the selected rows, the second decides how many you want to display.

so LIMIT 1 would start from the second row and show everything that the query returns.

where as LIMIT 0,1 would start from the first row and only show 1 record..

hth

[mrhappiness]

Originally posted by tuf-web.co.uk
so LIMIT 1 would start from the second row and show everything that the query returns.

no

LIMIT 1 and LIMIT 0, 1 are the very same

did you try the select in the console window?
you have an index on your colum 'v' used for comparison?

the column 'v' is of a string type?