NYCPHP Meetup

[Rob Marscher]

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

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.nyphp.org/pipermail/talk/attachments/20110104/82651a90/attachment.html>