Optimized way to get x Random rows satisfying given criteria in MySQL

I need to get x rows from a Database Table which satisfy some given criteria. I know that we can get random rows from MySQL using ORDER BY RAND ().

SELECT * FROM 'vids' WHERE 'cat'=n ORDER BY RAND() LIMIT x

I am looking for the most optimized way do the开发者_高级运维 same {Low usage of system resources is main priority. Next important priority is speed of the query}. Also, in the table design, should I make 'cat' INDEX ?

---

I'm trying to think of how to do this too. My thinking at the moment is the following three alternatives:

1) select random rows ignoring criteria, then throw out ones that do not match at the application level and select more random rows if needed. This method will be effective if your criteria matches lots of rows in your table, perhaps 20% or more (need to benchmark)

2) select rows following criteria, and choosing a row based on a random number between 1 and count(*) (random number determined in the application). This will be effective if the data matching the criteria is evenly distributed, but will fail terribly if for example you are selecting a date range, and the majority of random numbers will fall upon records outside this range.

3) my current favourite, but also the most work. For every combination of criteria you intend to use to select a random record, you insert a record into a special table for that criteria. You then select random records from the special table, and follow them back to your data. For example, you might have a table like this:

Table cat: name, age, eye_colour, fur_type

If you want to be able to select random cats with brown fur, then you need a table like this:

Table cats_with_brown_fur: id (autonumber), cat_fk

You can then select a random record from this table based on the autonumber id, and it will be fast, and will produce evenly distributed random results. But indeed, if you select from many sets of criteria, you will have some overheads on maintaining these tables.

That's my current take on it, anyway. Good luck

---

Order by Rand() is a bad idea.

Here's a better solution: How can i optimize MySQL's ORDER BY RAND() function?

Google is your friend, a lot of people have it explained it better than I ever could.
http://www.titov.net/2005/09/21/do-not-use-order-by-rand-or-how-to-get-random-rows-from-table/ http://www.phpbuilder.com/board/showthread.php?t=10338930 http://www.paperplanes.de/2008/4/24/mysql_nonos_order_by_rand.html