> 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 >> >> > > 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 3:44 PM, David Mintz wrote: > 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. Ah yes... so mysql is deciding to not use an index because you are selecting all of the rows from the event_types table, so therefore it doesn't think the index provides a benefit vs. a full table scan. Although, it needs the index for the joins. Try adding FORCE INDEX and see if it helps: EXPLAIN select event_types.id, event_types.name, count(events.id), count(requests.id) FROM event_types FORCE INDEX (PRIMARY) 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 Otherwise, you can do three queries: SELECT event_types.id, event_types.name FROM event_types; SELECT count(0) FROM requests GROUP BY event_type_id; SELECT count(0) FROM events GROUP BY event_type_id; And then merge the data together in php.
_______________________________________________ New York PHP Users Group Community Talk Mailing List http://lists.nyphp.org/mailman/listinfo/talk http://www.nyphp.org/Show-Participation
