Select one or multiple random SQL rows with a WHERE condition on a large table

Question

I've read http://explainextended.com/2009/03/01/selecting-random-rows/ that was also suggested as answer to other questions about selecting a random row from a large table.

However, I now wonder how this technique can be combined with selecting only the rows that also have another field set to a certain value.

For my case, I'm tyring to build a Markov chain. My table has an id column that is the auto-increment primary index. It also has a snippet column, that stores the new part of the string that is to be added, but only if the prev_snippet column is correct.

This means that I want to select a random row from the table that also has prev_snippet set to some string I already have. How can I do so, efficiently?

Answer 1

So yes, if you dont care about truly random, then the idea in that linked post, is probably pretty good. as long as index on the prev_snippet column!

But because its still completely indepentat use of random, you could end up getting no row in the result, even though there is one. ie each time the filter runs, it just happens to always exclude.

franky, would just try using ORDER BY RAND() LIMIT 1, it will probably work pretty well. its the size of the resultset that is most important, not the initial table size.