I ran into a problem today where I had SQL queries taking a really long time to execute and I had pages timing out. After a lot of investigation, I found a strange behavior that I didn’t expect that was killing my application.
I’m using Rails 2.1.1 and I have a named scope that looks like this:
The limited scope adds a “limit X” to the query to reduce the number of rows returned
In my view, I have some code that looks like this:
This code works great.
Except when the Foo table starts to get large.
In the MySQL slow log, I was seeing almost this generated query, except it looked like the code was running it as a count(). Since my table is quite large (hence only getting the first 10 items!), this count was taking a really long time.
Checking through the code, I find that the partial rendering code calls empty? on the collection. For Named Scopes, this partial optimization actually causes the Named Scope implementation to call count if it doesn’t yet have data, which causes a huge database hit in my case.
So how do I fix this?
I think the best way to fix this is to use the all method on the named scope object to cause the data to be loaded by the time the partial rendering checks for for empty?
Its interesting that for a count() query with a limit X that MySQL doesn’t stop when it gets to X, but ultimately, its the Rails code performing an optimization that causes a database access that is to blame here.