How to get a random sample in SQL

It can be a real pain to develop and test queries against really big tables. So, rather than query the entire table, you can use a simple trick to select a random sample (a smaller subset). Here's how:

select * from foo order by rand() limit sample_size

The "order by rand()" clause will (obviously) return the results in random order, while the "limit" clause will (obviously) limit the results sets you get back. If you want to get all scientific about it, you should pick a sample size that is related to the overall size of your table. There are several resources you can use, like this table of sample sizes.


Once you're query is working and you feel comfortable with the data you're getting back, then execute remove the order by and rand clauses.