MySQL select 10 random rows from 600K rows fast

by

A great post handling several cases, from simple, to gaps, to non-uniform with gaps.

http://jan.kneschke.de/projects/mysql/order-by-rand/

For most general case, here is how you do it:

SELECT name
  FROM random AS r1 JOIN
       (SELECT CEIL(RAND() *
                     (SELECT MAX(id)
                        FROM random)) AS id)
        AS r2
 WHERE r1.id >= r2.id
 ORDER BY r1.id ASC
 LIMIT 1

This supposes that the distribution of ids is equal, and that there can be gaps in the id list. See the article for more advanced examples

More Related Contents:

  1. quick selection of a random row from a large table in mysql
  2. Simple Random Samples from a Sql database
  3. How to select one row randomly taking into account a weight?
  4. quick selection of a random row from a large table in mysql
  5. Simple Random Samples from a MySQL Sql database
  6. Insert/ Update random date in MySQL
  7. MYSQL Select One Random record from each Category
  8. mysql unique number generation
  9. SQL Query needed for fetch value from Two identical table but not fully identical table
  10. SQL select only rows with max value on a column [duplicate]
  11. How to create a MySQL hierarchical recursive query?
  12. How do I query between two dates using MySQL?
  13. MySQL: @variable vs. variable. What’s the difference?
  14. Join between tables in two different databases?
  15. How to select a column name with a space in MySQL [duplicate]
  16. Can a table field contain a hyphen?
  17. How do I use on delete cascade in mysql?
  18. MySQL JOIN the most recent row only?
  19. MySQL Group By and Sum total value of other column
  20. Search all columns of a table using a single where condition with single keyword in mysql
  21. What does SQL clause “GROUP BY 1” mean?
  22. Syntax error when using except in a query
  23. Why the order of evaluation for expressions involving user variables is undefined?
  24. sql like operator on integer
  25. MySQL – SELECT * INTO OUTFILE LOCAL ?
  26. The best way to remove value from SET field?
  27. DELETE FROM `table` AS `alias` … WHERE `alias`.`column` … why syntax error?
  28. What happens when I exhaust a bigint generated key? How to handle it?
  29. MySQL update statement to store ranking positions
  30. is there a length limit to group_concat or another reason why it would not work on a text field

Leave a Comment