MySQL select 10 random rows from 600K rows fast?

Question

How can I best write a query that selects 10 rows randomly from a total of 600k?

Answer 1

The fastest way to select random rows from a MySQL database is to use the RAND function. The number of rows returned can be set using LIMIT. For example:

SELECT *

FROM table

ORDER BY RAND ()

LIMIT 10;

Exactly how fast is fast depends on how the tables are indexed. You can view the execution time and the index used by preceding the query with EXPLAIN ANALYZE.