execute a select statement with order of rows randomized every execute?

[Winston Liek]

Hi guys,

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?

[Martin Vashko]

I'm not sure it will work, but you can try:
... order by rand()

[Roel De Nijs]

Here you'll find a great post handling several cases, from simple, to gaps, to non-uniform with gaps.

[Martin Vashko]

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.

[Roel De Nijs]

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.