# Slow ActiveRecord - MySQL

**URL:** <https://www.ruby-forum.com/t/slow-activerecord-mysql/75441>\
**Category:** Rails\
**Created:** [November 9, 2006, 11:00pm UTC](https://www.ruby-forum.com/t/slow-activerecord-mysql/75441 "2006-11-09T23:00:41Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![casper\_the\_ghost](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/c/58956e/32.png) [@casper\_the\_ghost](https://www.ruby-forum.com/u/casper_the_ghost)\
**Post date:** [November 9, 2006, 11:00pm UTC](https://www.ruby-forum.com/t/slow-activerecord-mysql/75441/1 "2006-11-09T23:00:41Z")

</div>

I use find\_by\_sql to execute a query which takes around 120 seconds to  
execute in Ruby. When I execute the same request using SQLyog (GUI),  
the requests takes around 15 seconds to execute.

Here’s roughly what the query looks like:

SELECT DAY(date\_time) “day”, COUNT( \* ) “count”  
FROM table  
WHERE (id = some\_id)  
AND(YEAR(date\_time) = 2006)  
AND(MONTH(date\_time) = 8)  
AND(some\_string IN (‘some short string’) )  
GROUP BY DAY( date\_time );

I’m querying about a million records.

---

<div class="post-metadata">

**Author:** ![casper\_the\_ghost](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/c/58956e/32.png) [@casper\_the\_ghost](https://www.ruby-forum.com/u/casper_the_ghost)\
**Post date:** [November 9, 2006, 11:12pm UTC](https://www.ruby-forum.com/t/slow-activerecord-mysql/75441/2 "2006-11-09T23:12:44Z")

</div>

On 11/9/06, removed\_email\_address@domain.invalid [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid)  
wrote:

> AND(YEAR(date\_time) = 2006)  
> AND(MONTH(date\_time) = 8)  
> AND(some\_string IN (‘some short string’) )  
> GROUP BY DAY( date\_time );
> 
> I’m querying about a million records.

How many results? Compare find\_by\_sql with connection.select\_all  
timing.

An index such as (id, YEAR(date\_time), MONTH(date\_time)) would really  
help,  
in any case.

jeremy

---

<div class="post-metadata">

**Author:** ![casper\_the\_ghost](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/c/58956e/32.png) [@casper\_the\_ghost](https://www.ruby-forum.com/u/casper_the_ghost)\
**Post date:** [November 10, 2006, 4:32pm UTC](https://www.ruby-forum.com/t/slow-activerecord-mysql/75441/3 "2006-11-10T16:32:43Z")

</div>

Around 6. select\_all gave results similar to SQLyog (around 15  
seconds).

It’s not possible to use YEAR(date\_time) as an index, is it? If I alter  
my table to have different columns for the year, month and day, will  
there be a good performance gain?

And while this is all good advice, I still don’t understand why Rails  
would have such problems with the query.

---

<div class="post-metadata">

**Author:** ![casper\_the\_ghost](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/c/58956e/32.png) [@casper\_the\_ghost](https://www.ruby-forum.com/u/casper_the_ghost)\
**Post date:** [November 10, 2006, 5:16pm UTC](https://www.ruby-forum.com/t/slow-activerecord-mysql/75441/4 "2006-11-10T17:16:49Z")

</div>

-----BEGIN PGP SIGNED MESSAGE-----  
Hash: SHA1

Heist wrote:

> SELECT DAY(date\_time) “day”, COUNT( \* ) “count”  
> FROM table  
> WHERE (id = some\_id)  
> AND(YEAR(date\_time) = 2006)  
> AND(MONTH(date\_time) = 8)  
> AND(some\_string IN (‘some short string’) )  
> GROUP BY DAY( date\_time );

> Around 6. select\_all gave results similar to SQLyog (around 15  
> seconds).
> 
> It’s not possible to use YEAR(date\_time) as an index, is it?

Nope. You’re losing ability for the query optimizer to take advantages  
of indexes, by determining the name of your column using a  
function. Functions should always go on the right side of a comparison  
in a query, so you can take advantage of indexes.

The way you’re doing the query above forces that you’re going to do a  
FULL table scan each time.

> If I alter  
> my table to have different columns for the year, month and day, will  
> there be a good performance gain?

Yes, and add indexes on those columns.

> And while this is all good advice, I still don’t understand why Rails  
> would have such problems with the query.

ActiveRecord doesn’t have to bad of overhead for running a query.  
ActiveRecord thinly wraps a MySQL database connection. The  
overhead that ActiveRecord adds is in instantiating all of the results  
into ActiveRecord model instances. This is why in the first  
response to your post I commented on NOT trying to query results where  
the result set was a million objects.

I dont’ think you’re seeing an ActiveRecord issue here, now that I read  
your query more closely, the query needs to be written  
better and you need to use take advantage of indexes.

Zach

-----BEGIN PGP SIGNATURE-----  
Version: GnuPG v1.4.2.2 (GNU/Linux)  
Comment: Using GnuPG with Mozilla - [http://enigmail.mozdev.org](http://enigmail.mozdev.org)

iD8DBQFFVKZtMyx0fW1d8G0RAkzcAJ48BaW+o/XJwvCecPrnFC3WTsgf3ACfcxNj  
etliGjLlH/i78WnvhjlnZCk=  
=8lma  
-----END PGP SIGNATURE-----