NYCPHP Meetup

[David Mintz]

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

and it is so incredibly slow that I tire of waiting and abort the query. If
I JOIN just one table or the other, no problem. But both -- not good.

I have tried running EXPLAIN SELECT... and this is what I get:

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.idGROUP 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    | ALL  | NULL          | NULL | NULL    |
NULL |  4946 |                                 |
|  1 | SIMPLE      | events      | ALL  | NULL          | NULL | NULL    |
NULL | 63778 |                                 |
+----+-------------+-------------+------+---------------+------+---------+------+-------+---------------------------------+

and though I know but little about interpreting the output of EXPLAIN
SELECT.... I can see that mysql is not using any indexes.
But the columns I am JOINing on are the primary keys and I thought that
implies index, doesn't it? If we look at show index we get

mysql> show index from event_types;
+-------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table       | Non_unique | Key_name | Seq_in_index | Column_name |
Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| event_types |          0 | PRIMARY  |            1 | id          |
A         |          46 |     NULL | NULL   |      | BTREE      |         |
| event_types |          0 | idx_name |            1 | name        |
A         |          46 |     NULL | NULL   |      | BTREE      |         |
+-------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
2 rows in set (0.00 sec)

mysql> show index from events;
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table  | Non_unique | Key_name | Seq_in_index | Column_name | Collation |
Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| events |          0 | PRIMARY  |            1 | id          | A
|       63778 |     NULL | NULL   |      | BTREE      |         |
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
1 row in set (0.00 sec)

mysql> show index from requests;
+----------+------------+---------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+
| Table    | Non_unique | Key_name      | Seq_in_index | Column_name   |
Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+----------+------------+---------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+
| requests |          0 | PRIMARY       |            1 | id            |
A         |        4946 |     NULL | NULL   |      | BTREE      |         |
| requests |          0 | uniqueRequest |            1 | date          |
A         |         706 |     NULL | NULL   | YES  | BTREE      |         |
| requests |          0 | uniqueRequest |            2 | time          |
A         |        4946 |     NULL | NULL   | YES  | BTREE      |         |
| requests |          0 | uniqueRequest |            3 | judge_id      |
A         |        4946 |     NULL | NULL   |      | BTREE      |         |
| requests |          0 | uniqueRequest |            4 | event_type_id |
A         |        4946 |     NULL | NULL   |      | BTREE      |         |
| requests |          0 | uniqueRequest |            5 | language_id   |
A         |        4946 |     NULL | NULL   |      | BTREE      |         |
| requests |          0 | uniqueRequest |            6 | docket        |
A         |        4946 |     NULL | NULL   |      | BTREE      |         |
| requests |          1 | evt_id        |            1 | event_id      |
A         |        4946 |     NULL | NULL   |      | BTREE      |         |
+----------+------------+---------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+
8 rows in set (0.00 sec)


I have been googling and came across a rather old article here --
http://www.devshed.com/c/a/MySQL/MySQL-Optimization-part-1/2/ -- that says
to use "USE INDEX(something)" in the query but I have tried that several
ways and all result in a syntax error.

Of course I am thinking of working around, with either multiple queries or
maybe subselects. But I can't help but think there is some ninja magic that
would make this work.

Ultimately, the objective is to display some rows and figure out whether
they have children, which in turn will determine whether to display a
"Delete" thingy to the user.

Any ideas?

Thanks,

-- 
David Mintz
http://davidmintz.org/
It ain't over:
http://www.healthcare-now.org/
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.nyphp.org/pipermail/talk/attachments/20110104/a222f24a/attachment.html>