In which order Rails does the DB queries

Question

In http://stackoverflow.com/questions/2170001/select-n-objects-randomly-with-condition-in-rails Anurag kindly proposed this answer to randomly select n posts with votes >= x

Post.all(:conditions => ["votes >= ?", x], :order => "rand()", :limit => n)

my concern is that the number of posts that have more than x votes is very big.

what is the order the DB apply this criteria to the query?

Does it

• (a) select n posts with votes > x and then randomises it? or
• (b) select all posts with votes > x and then randomises and then selects the n first posts?
• (c) other?

Answer 1

Look in development.log for the generated query, should give you a clue.

Answer 2

The recommendation to check the development log is very useful.

However, in this case, the randomisation is happening on the MySQL end, not inside Active Record. In order to see how the query is being run inside MySQL, you can copy the query from the log and paste it into your MySQL tool of choice (console, GUI, whatever) and add "EXPLAIN" to the front of it.

You should end up with something like:

EXPLAIN SELECT * FROM posts WHERE votes >= 'x' ORDER BY rand() LIMIT n 

When I try a similar query in MySQL, I am told:

Select Type: SIMPLE
Using where; Using temporary; Using filesort

Then you should do a search for some of the excellent advice on SO on how to optimise MySQL queries. If there is an issue, adding an index on the votes column may improve performance. situation.

Answer 3

As Toby already pointed out, this is purely up to SQL server, everything being done in the query itself.

However, I am afraid that you can't get truly randomized output unless the database gets the whole resultset first, and then randomises it. Although, you should check the EXPLAIN result anyway.