NYCPHP Meetup

[Dan Cech]

Rob,

On 1/4/2011 4:28 PM, Rob Marscher wrote:
> insert into parent (name) values ('One'),('Two'),('Three');
> insert into child1 (parent_id) values (1),(1),(1),(3);
> insert into child2 (parent_id) values (2),(2),(2),(3);
>
> explain select parent.id, parent.name, count(child1.id), count(child2.id)
> from parent force index (primary)
> left join child1 on parent.id = child1.parent_id
> left join child2 on parent.id = child2.parent_id
> group by parent.id;
>
> +----+-------------+--------+-------+---------------+---------+---------+--------------------------+------+-------+
> | id | select_type | table  | type  | possible_keys | key     | key_len | ref                      | rows | Extra |
> +----+-------------+--------+-------+---------------+---------+---------+--------------------------+------+-------+
> |  1 | SIMPLE      | parent | index | NULL          | PRIMARY | 4       | NULL                     |    1 |       |
> |  1 | SIMPLE      | child1 | ref   | parent        | parent  | 4       | brightbu_namco.parent.id |    2 |       |
> |  1 | SIMPLE      | child2 | ref   | parent        | parent  | 4       | brightbu_namco.parent.id |    2 |       |
> +----+-------------+--------+-------+---------------+---------+---------+--------------------------+------+-------+
> +----+-------+------------------+------------------+
> | id | name  | count(child1.id) | count(child2.id) |
> +----+-------+------------------+------------------+
> |  1 | One   |                3 |                0 |
> |  2 | Two   |                0 |                3 |
> |  3 | Three |                1 |                1 |
> +----+-------+------------------+------------------+

This is demonstrating 2 special cases which will work properly:

1. no rows for a parent in one of the child tables
2. 1 row for a parent in both child tables

If you run your child inserts again to create more rows you'll end up 
with a count of 4 instead of 2 in the 3rd row.

If you remove the count()s you can see why this is the case:

SELECT parent.id, parent.name, child1.id, child2.id
FROM parent FORCE INDEX (PRIMARY )
LEFT JOIN child1 ON parent.id = child1.parent_id
LEFT JOIN child2 ON parent.id = child2.parent_id;

+----+-------+------+------+
| id | name  | id   | id   |
+----+-------+------+------+
|  1 | One   |    1 | NULL |
|  1 | One   |    2 | NULL |
|  1 | One   |    3 | NULL |
|  1 | One   |    5 | NULL |
|  1 | One   |    6 | NULL |
|  1 | One   |    7 | NULL |
|  2 | Two   | NULL |    1 |
|  2 | Two   | NULL |    2 |
|  2 | Two   | NULL |    3 |
|  2 | Two   | NULL |    5 |
|  2 | Two   | NULL |    6 |
|  2 | Two   | NULL |    7 |
|  3 | Three |    4 |    4 |
|  3 | Three |    4 |    8 |
|  3 | Three |    8 |    4 |
|  3 | Three |    8 |    8 |
+----+-------+------+------+

Dan