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
_______________________________________________
New York PHP Users Group Community Talk Mailing List
http://lists.nyphp.org/mailman/listinfo/talk
http://www.nyphp.org/Show-Participation
