CODEWITHSUNDEEP
sqlruby-on-railsruby
Victor
Victor

Reputation: 23922

In which order Rails does the DB queries

In 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

Upvotes: 1

Views: 102

Answers (3)

Mladen Jablanović
Mladen Jablanović

Reputation: 44080

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.

Upvotes: 1

Toby Hede
Toby Hede

Reputation: 37123

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.

Upvotes: 3

JRL
JRL

Reputation: 77995

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

Upvotes: 0

Related Questions