Hello All,
I'm trying to optimize the following query and don't understand why the
second line in the EXPLAIN result is type "ALL" and is not using the
index on the primary key column in the individual table - can anybody
shed any light?
mysql> EXPLAIN SELECT individual.id, individual.name_first,
individual.name_last FROM groups, individual_groups, individual WHERE
groups.id = '98' AND individual_groups.group_id = groups.id AND
individual_groups.individual_id = individual.id ORDER BY name_last,
name_first;
+-------------------+--------+---------------+---------+---------
+---------------------+------+----------------+
| table | type | possible_keys | key | key_len | ref
| rows | Extra |
+-------------------+--------+---------------+---------+---------
+---------------------+------+----------------+
| groups | const | PRIMARY | PRIMARY | 4 |
const | 1 | Using filesort |
| individual | ALL | PRIMARY | NULL | NULL | NULL
| 2121 | |
| individual_groups | eq_ref | PRIMARY | PRIMARY | 8 |
individual.id,const | 1 | Using index |
+-------------------+--------+---------------+---------+---------
+---------------------+------+----------------+
mysql> show index from individual;
+------------+------------+------------+--------------+-------------
+-----------+-------------+----------+--------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name |
Collation | Cardinality | Sub_part | Packed | Comment |
+------------+------------+------------+--------------+-------------
+-----------+-------------+----------+--------+---------+
| individual | 0 | PRIMARY | 1 | id | A
| 2121 | NULL | NULL | |
| individual | 1 | name_first | 1 | name_first | A
| 1060 | 7 | NULL | |
| individual | 1 | name_last | 1 | name_last | A
| 1060 | 5 | NULL | |
+------------+------------+------------+--------------+-------------
+-----------+-------------+----------+--------+---------+
Regards,
Chris Fowler
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
