4

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 ?

OMG Ponies
  • 325,700
  • 82
  • 523
  • 502
Lucky Murari
  • 12,672
  • 5
  • 22
  • 43
  • possible duplicate of [MySQL: Alternatives to ORDER BY RAND()](http://stackoverflow.com/questions/1823306/mysql-alternatives-to-order-by-rand) – OMG Ponies Feb 14 '11 at 15:27

2 Answers2

0

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

fabspro
  • 1,639
  • 19
  • 29
  • I'm also considering using offset and limit, like this: get the count(*) of records matching criteria, then one by one, repeatedly call a query like this: select __ LIMIT offset, 1 calculate offset as a random number between 0 and the count. I am not completely sure how this works performance wise, but I would say that if you have proper indexes, then using a few queries and jumping around with offsets won't be too bad. – fabspro Jul 24 '12 at 16:11
-3

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

Community
  • 1
  • 1
rcarver
  • 963
  • 9
  • 13