Slow ActiveRecord - MySQL

Rails
1

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.

2

On 11/9/06, [email protected] [email protected]
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

3

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.

4

-----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

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