NYCPHP Meetup

[David Mintz]

On Tue, Jan 4, 2011 at 1:33 PM, Rob Marscher <rmarscher at beaffinitive.com>wrote:

> Looks like you need an index on events.event_type_id
> and requests.event_type_id.  I'm not seeing those in your indexes.  I see
> event_type_id is part of the uniqueRequest index, but it can't use it unless
> it's the first column in the index or you specify the columns that come
> before it in your index in your where clause.
>
> Sometimes queries like this are better off being split into multiple
> queries... but I think in this case if you just add the two event_type_id
> indexes, you should be fine.
>
> -Rob
>
> On Jan 4, 2011, at 1:05 PM, David Mintz wrote:
>
>
> I am trying to do something like this:
>
> SELECT parent.id, parent.someColumn, count(child_table_1.id), count(
> child_table_2.id) FROM parent
> LEFT JOIN child_table_1 ON child_table_1.parent_id = parent.id
> LEFT JOIN child_table_2 ON child_table_2.parent_id = parent.id
>
>
>
Thanks. Don't know why I missed that. So I did it and now EXPLAIN tells me
(apologies for the formatting)

mysql> EXPLAIN select event_types.id, event_types.name, count(events.id),
count(requests.id) FROM event_types LEFT JOIN requests ON
requests.event_type_id = event_types.id  LEFT JOIN events ON
events.event_type_id = event_types.id GROUP BY event_types.id;
+----+-------------+-------------+------+------------------+------------------+---------+-----------------------+------+---------------------------------+
| id | select_type | table       | type | possible_keys    |
key              | key_len | ref                   | rows |
Extra                           |
+----+-------------+-------------+------+------------------+------------------+---------+-----------------------+------+---------------------------------+
|  1 | SIMPLE      | event_types | ALL  | NULL             |
NULL             | NULL    | NULL                  |   46 | Using temporary;
Using filesort |
|  1 | SIMPLE      | requests    | ref  | event_type_index |
event_type_index | 2       | shitou.event_types.id |  236
|                                 |
|  1 | SIMPLE      | events      | ref  | event_type_index |
event_type_index | 2       | shitou.event_types.id | 1417
|                                 |
+----+-------------+-------------+------+------------------+------------------+---------+-----------------------+------+---------------------------------+

the point being that mysql apparently still doesn't want to make use of the
index on event_types.id. I tried running the query again and waited for a
few minutes for it to return some results. Something weird going on with my
mysql installation, or the computer itself, maybe? This old dog is
memory-poor (1 GB). I am gonna move on and try something else.

Thanks again.

-- 
David Mintz
http://davidmintz.org/
It ain't over:
http://www.healthcare-now.org/
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.nyphp.org/pipermail/talk/attachments/20110104/a0ee0646/attachment.html>