MySQL Select Random X Entries - Optimized

Question

MySQL what's the best way to select X random entries (rather than just one) - optimization for heavy use, i.e. on main page of a domain.

Supposedly just blindly using MySQL rand() is going to make this rather scary for large databases - please give me a better optimization answer than that!

Answer 1

the solution is use php

look at this article that choose the solution number 3 as faster

http://akinas.com/pages/en/blog/mysql_random_row/

Solution 3 [PHP]

$offset_result = mysql_query( " SELECT FLOOR(RAND() * COUNT(*)) AS `offset` FROM `table` ");
$offset_row = mysql_fetch_object( $offset_result );
$offset = $offset_row->offset;
$result = mysql_query( " SELECT * FROM `table` LIMIT $offset, 1 " )

the Solution 4 [SQL] (Second in fast)

SELECT * FROM `table` WHERE id >= (SELECT FLOOR( MAX(id) * RAND()) FROM `table` ) ORDER BY id LIMIT 1;