NYCPHP Meetup

[Rob Marscher]

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 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
> 
> 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.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    | 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/c96dfc1e/attachment.html>