quick selection of a random row from a large table in mysql

by

Grab all the id’s, pick a random one from it, and retrieve the full row.

If you know the id’s are sequential without holes, you can just grab the max and calculate a random id.

If there are holes here and there but mostly sequential values, and you don’t care about a slightly skewed randomness, grab the max value, calculate an id, and select the first row with an id equal to or above the one you calculated. The reason for the skewing is that id’s following such holes will have a higher chance of being picked than ones that follow another id.

If you order by random, you’re going to have a terrible table-scan on your hands, and the word quick doesn’t apply to such a solution.

Don’t do that, nor should you order by a GUID, it has the same problem.

More Related Contents:

  1. How to select one row randomly taking into account a weight?
  2. quick selection of a random row from a large table in mysql
  3. Recreating a query using INNER JOIN? [duplicate]
  4. What is better subquery vs literal on IN clause in mysql
  5. SQL query return data from multiple tables
  6. MySQL select 10 random rows from 600K rows fast
  7. MySQL SELECT only not null values
  8. Ordering by specific field value first
  9. SELECT / GROUP BY – segments of time (10 seconds, 30 seconds, etc)
  10. Simple Random Samples from a Sql database
  11. Checking multiple columns for one value
  12. How does MySQL’s ORDER BY RAND() work?
  13. MySQL #1093 – You can’t specify target table ‘giveaways’ for update in FROM clause
  14. MySQL INSERT INTO … VALUES and SELECT
  15. MySQL error 1241: Operand should contain 1 column(s)
  16. Compare only day and month with date field in mysql
  17. Syntax error when using except in a query
  18. SELECT query return 1 row from each group
  19. MySQL INNER JOIN select only one row from second table
  20. Simple Random Samples from a MySQL Sql database
  21. MySQL Select Query – Get only first 10 characters of a value
  22. SQL query to check if a name begins and ends with a vowel
  23. Insert/ Update random date in MySQL
  24. How to SELECT by MAX(date)?
  25. MYSQL Select One Random record from each Category
  26. mysql unique number generation
  27. How to select a column name with a space in MySQL [duplicate]
  28. MySQL: Select Random Entry, but Weight Towards Certain Entries
  29. How do I use on delete cascade in mysql?
  30. Return a default value if single row is not found

Leave a Comment