Simple Random Samples from a MySQL Sql database

by

I think the fastest solution is 

select * from table where rand() <= .3

Here is why I think this should do the job.

  • It will create a random number for each row. The number is between 0 and 1
  • It evaluates whether to display that row if the number generated is between 0 and .3 (30%).

This assumes that rand() is generating numbers in a uniform distribution. It is the quickest way to do this.

I saw that someone had recommended that solution and they got shot down without proof.. here is what I would say to that –

  • This is O(n) but no sorting is required so it is faster than the O(n lg n)

  • mysql is very capable of generating random numbers for each row. Try this –

    select rand() from INFORMATION_SCHEMA.TABLES limit 10;

Since the database in question is mySQL, this is the right solution.

More Related Contents:

  1. MySQL select 10 random rows from 600K rows fast
  2. quick selection of a random row from a large table in mysql
  3. Simple Random Samples from a Sql database
  4. How to select one row randomly taking into account a weight?
  5. quick selection of a random row from a large table in mysql
  6. Insert/ Update random date in MySQL
  7. MYSQL Select One Random record from each Category
  8. mysql unique number generation
  9. get first occurrence of last changed value of a column [closed]
  10. MySQL – Rows to Columns
  11. ROW_NUMBER() in MySQL
  12. multiple query same table but in different columns mysql
  13. How can I make SQL case sensitive string comparison on MySQL?
  14. How can I temporarily disable a foreign key constraint in MySQL?
  15. How to do a batch insert in MySQL
  16. Count the occurrences of DISTINCT values
  17. Creating a “Numbers Table” in MySQL
  18. MySQL Group by SUM
  19. Set NOW() as Default Value for datetime datatype?
  20. In SQL, how to select the top 2 rows for each group
  21. Mysql query to dynamically convert rows to columns on the basis of two columns
  22. mysql trigger stored trigger is already used by statement which invoked stored trigger
  23. Error Dropping Database (Can’t rmdir ‘.test\’, errno: 17)
  24. MySQL: How do I join same table multiple times?
  25. mysql update query with sub query
  26. What is the difference between the backtick and the square bracket in SQL statements?
  27. Mysql: Order by like?
  28. Getting first day of the week in MySql using Week No
  29. SQL date format convert? [dd.mm.yy to YYYY-MM-DD]
  30. ORDER BY alphabet first then follow by number

Leave a Comment