Disabling the Rails query cache

December 1st, 2009

Yes, Rails has a query cache (in addition to the one your database probably has).

I discovered this when trying to debug a problem with a query that should have been giving me random rows from the database. The unit test worked fine, so I didn’t bother testing for randomness in the controller test, since it’s annoying to test for randomness. But of course it didn’t work in real life. It’s kind of ironic to have a bug that the results are not unexpected enough.

Hey, let’s look at the logs:

  Goal Load (0.000656)    SELECT SQL_NO_CACHE g.* FROM goals g ORDER BY RAND() LIMIT 100 
  CACHE (0.000000)     SELECT SQL_NO_CACHE g.* FROM goals g ORDER BY RAND() LIMIT 100
  CACHE (0.000000)     SELECT SQL_NO_CACHE g.* FROM goals g ORDER BY RAND() LIMIT 100

Hmm. So it turns out that Rails/ActiveRecord has its own query cache. It invisibly does what you want, unless you are doing a query that returns random results more than once in a request. The documentation is a bit thin, but it’s easy enough to disable temporarily:

1
2
3
4
5

 def self.random_goals(limit = 100)
      uncached do # disable rails query cache
          Goal.find_by_sql [RANDOM_GOALS_SQL, limit]
      end
  end
Posted by laurel Filed in Code

3 Responses to “Disabling the Rails query cache”

  1. Brendan Says:
    December 1st, 2009 at 05:09 PM Isn't ORDER BY RAND() generally frowned upon?
  2. Laurel Fan Says:
    December 2nd, 2009 at 08:45 AM Yeah, it is. We really should change this query to generate random numbers in Ruby code and do a select where ids in (...).
  3. Paolo Says:
    December 14th, 2009 at 01:10 AM That could return less records than the limit because you might generate ids for records that do not exist anymore in the db. There are alternative strategies but no really good ones unless you leave the db do it with order by rand() which is a bad sorting clause, I know. You can find a lot of posts on the subject by searching sql random records.

Sorry, comments are closed for this article.