On Tue, Jan 4, 2011 at 1:33 PM, Rob Marscher <rmarsc...@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/
_______________________________________________ New York PHP Users Group Community Talk Mailing List http://lists.nyphp.org/mailman/listinfo/talk http://www.nyphp.org/Show-Participation
