On Feb 26, 2009, at 12:01 PM, Amanda Waite wrote:
SELECT count(DISTINCT `events`.id) AS count_all FROM `events` LEFT
OUTER JOIN `addresses` ON `addresses`.id = `events`.address_id WHERE
(event_date >= '2009-02-26')
was really having an effect on overall DB CPU %usr time. Where I was
going from 28% to 54% %usr time over a 600 sec run, after modifying
the event_controller to not include the address when paginating the
events in the index action it went from 21% to 29% during the same
length run. It still creeps up, but less so.
Interesting, this looks like it would have to be doing a table scan or
is not using the correct index. This is what I get with MySQL, can you
do the same on your database?
mysql> explain SELECT count(DISTINCT `events`.id) AS count_all FROM
`events` LEFT OUTER JOIN `addresses` ON `addresses`.id =
`events`.address_id WHERE (event_date >= '2009-02-26')
-> ;
+----+-------------+-----------+--------+----------------------------
+---------+---------+------------------------------------+------
+-------------+
| id | select_type | table | type | possible_keys |
key | key_len | ref | rows |
Extra |
+----+-------------+-----------+--------+----------------------------
+---------+---------+------------------------------------+------
+-------------+
| 1 | SIMPLE | events | ALL | index_events_on_event_date |
NULL | NULL | NULL | 55 | Using
where |
| 1 | SIMPLE | addresses | eq_ref | PRIMARY |
PRIMARY | 4 | perf_development.events.address_id | 1 | Using
index |
+----+-------------+-----------+--------+----------------------------
+---------+---------+------------------------------------+------
+-------------+
2 rows in set (0.03 sec)
This looks correct, it's using the date index and then doing an eq-
join on using the address' primary index. If we don't fetch the
address in the :include it will be an extra query for each event. The
issue may be the increased amount of data returned because of the
product of the two tables. In 2.2.2 they changed the :include syntax
to do it as a two part where they in first request the primary table
and then the secondary as an in. I'll check out why it chose to
execute the query this way.
Cheers,
- Will Sobel
