execute a select statement with order of rows randomized every execute?
posted 1 year ago
I have an mysql query and it returns around 500 rows and is executed multiple times.
How can I randomize the position of rows returned by query each time I execute it?
I'm not sure if it's possible in mySQL statement but if not, how can I randomize a list?
That's interesting, although I think it deals with cases when you want to pick one (or a generally a few) rows randomly out of a large table. In that case, sorting the entire table to throw all but a few rows away is really needlessly expensive.
If the goal is to fetch all rows from a query, ordered randomly, then I'd say that the simple order by rand()should be quite effective, for a reasonable amount of rows (the 500 rows in the original post still seem "reasonable" to me). Fetching out millions of rows in random order this way certainly wouldn't be a good idea.
In Microsoft SQL Server you can do something similar (and we use it in our tests). Using TABLESAMPLE you can limit the number of rows returned from a table in the FROM clause to a sample number or PERCENT of rows. Combined with the REPEATABLE option you can choose to return the same rows or return a different set of rows using another seed value.