# Slow ActiveRecord - MySQL

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

<div class="post-metadata">

**Author:** ![Heist](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/h/67e7ee/32.png) [@Heist](https://www.ruby-forum.com/u/Heist)\
**Post date:** [November 9, 2006, 11:02pm UTC](https://www.ruby-forum.com/t/slow-activerecord-mysql/75443/1 "2006-11-09T23:02:42Z")

</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:** ![Heist](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/h/67e7ee/32.png) [@Heist](https://www.ruby-forum.com/u/Heist)\
**Post date:** [November 9, 2006, 11:16pm UTC](https://www.ruby-forum.com/t/slow-activerecord-mysql/75443/2 "2006-11-09T23:16:45Z")

</div>

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

Heist wrote:

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

Do you need all of the million records in memory at once? You’re getting  
overhead of ActiveRecord creating one million records and  
keeping them in memory, and your program is probably ending up using  
swap which is forcing it to take sooo long.

I often query a few million records, but I don’t pull them all into  
memory a once, it’s too inefficient, and not just in ruby.

Zach  
ActiveRecord::Extensions - [http://www.continuousthinking.com/are](http://www.continuousthinking.com/are)

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

iD8DBQFFU6hbMyx0fW1d8G0RAliUAJ9ZKjCgqjkkKV/woBQP8ZQl5LHyxACeOzW7  
c2QQUvmdeg4bapbkz6OMepg=  
=lvq7  
-----END PGP SIGNATURE-----

---

<div class="post-metadata">

**Author:** ![Heist](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/h/67e7ee/32.png) [@Heist](https://www.ruby-forum.com/u/Heist)\
**Post date:** [November 9, 2006, 11:54pm UTC](https://www.ruby-forum.com/t/slow-activerecord-mysql/75443/3 "2006-11-09T23:54:52Z")

</div>

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

> Do you need all of the million records in memory at once? You’re getting  
> overhead of ActiveRecord creating one million records and  
> keeping them in memory, and your program is probably ending up using swap  
> which is forcing it to take sooo long.
> 
> I often query a few million records, but I don’t pull them all into memory  
> a once, it’s too inefficient, and not just in ruby.

I think he’s saying there are a million rows in the table. A million  
results  
grouped by day would mean he has records spanning nearly three millenia  
😉

jeremy

---

<div class="post-metadata">

**Author:** ![Heist](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/h/67e7ee/32.png) [@Heist](https://www.ruby-forum.com/u/Heist)\
**Post date:** [November 10, 2006, 12:08am UTC](https://www.ruby-forum.com/t/slow-activerecord-mysql/75443/4 "2006-11-10T00:08:55Z")

</div>

On Nov 9, 2006, at 11:14 PM, zdennis wrote:

> I often query a few million records, but I don’t pull them all into  
> memory a once, it’s too inefficient, and not just in ruby.

What do you do then? Paginate by hand?

– fxn

---

<div class="post-metadata">

**Author:** ![Heist](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/h/67e7ee/32.png) [@Heist](https://www.ruby-forum.com/u/Heist)\
**Post date:** [November 10, 2006, 1:43am UTC](https://www.ruby-forum.com/t/slow-activerecord-mysql/75443/5 "2006-11-10T01:43:02Z")

</div>

> On Nov 9, 2006, at 11:14 PM, zdennis wrote:
> 
> > I often query a few million records, but I don’t pull them all into  
> > memory a once, it’s too inefficient, and not just in ruby.
> 
> What do you do then? Paginate by hand?

Yes 🙂

---

<div class="post-metadata">

**Author:** ![Heist](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/h/67e7ee/32.png) [@Heist](https://www.ruby-forum.com/u/Heist)\
**Post date:** [November 10, 2006, 5:18pm UTC](https://www.ruby-forum.com/t/slow-activerecord-mysql/75443/6 "2006-11-10T17:18:50Z")

</div>

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

Jeremy K. wrote:

> I think he’s saying there are a million rows in the table. A million  
> results grouped by day would mean he has records spanning nearly three  
> millenia 😉

My bad. =)

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)

iD8DBQFFVKavMyx0fW1d8G0RAmUTAJ41LSS002nIj25G4I3OvgzJYA2YVACfXKwD  
hPzN8yzBDhE1For8BWYS0Xg=  
=8rut  
-----END PGP SIGNATURE-----