Index question

[Willem Bison]

I have a table PERSON that contains a reference to another table CLAN:
  cid smallint(6)
and a flag
  deleted enum('Y','N')
(Below is the full table description)

cid points to the primary key of CLAN (auto-increment, smallint).

I added indices to PERSON for cid, deleted and (cid,deleted).

mysql> explain select person.nshort from person,clan where
person.cid=clan.id and person.deleted='N';
+--------+--------+--------------------+---------+---------+------------+---
---+-------------+
| table  | type   | possible_keys      | key     | key_len | ref        |
rows | Extra       |
+--------+--------+--------------------+---------+---------+------------+---
---+-------------+
| person | ALL    | deleted,cid,cidact | NULL    |    NULL | NULL       |
9048 | where used  |
| clan   | eq_ref | PRIMARY,id         | PRIMARY |       2 | person.cid |
1 | Using index |
+--------+--------+--------------------+---------+---------+------------+---
---+-------------+

- Why is no index on PERSON used ?
- Was I right in adding a multiple index (cid,deleted) for queries where
both 'cid' and 'deleted' are tested ?





mysql> desc person;
+-------------+----------------------+------+-----+---------+---------------
-+
| Field       | Type                 | Null | Key | Default | Extra
|
+-------------+----------------------+------+-----+---------+---------------
-+
| cid         | smallint(6)          |      | MUL | 0       |
|
| id          | smallint(6)          |      | PRI | 0       | auto_increment
|
| deleted     | enum('Y','N')        |      | MUL | N       |
|
< irrelevant stuff deleted >
+-------------+----------------------+------+-----+---------+---------------
-+
27 rows in set (0.05 sec)

mysql> show index from person;
+--------+------------+----------+--------------+-------------+-----------+-
------------+----------+
| Table  | Non_unique | Key_name | Seq_in_index | Column_name | Collation |
Cardinality | Sub_part |
+--------+------------+----------+--------------+-------------+-----------+-
------------+----------+
| person |          0 | PRIMARY  |            1 | id          | A         |
9048 |     NULL |
| person |          1 | id       |            1 | id          | A         |
NULL |     NULL |
| person |          1 | deleted  |            1 | deleted     | A         |
NULL |     NULL |
| person |          1 | cid      |            1 | cid         | A         |
NULL |     NULL |
| person |          1 | cidact   |            1 | cid         | A         |
NULL |     NULL |
| person |          1 | cidact   |            2 | deleted     | A         |
NULL |     NULL |
+--------+------------+----------+--------------+-------------+-----------+-
------------+----------+
7 rows in set (0.07 sec)

mysql> show index from clan;
+-------+------------+----------+--------------+-------------+-----------+--
-----------+----------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation |
Cardinality | Sub_part |
+-------+------------+----------+--------------+-------------+-----------+--
-----------+----------+
| clan  |          0 | PRIMARY  |            1 | id          | A         |
1510 |     NULL |
| clan  |          1 | id       |            1 | id          | A         |
NULL |     NULL |
| clan  |          1 | one      |            1 | one         | A         |
NULL |     NULL |
| clan  |          1 | deleted  |            1 | deleted     | A         |
NULL |     NULL |
+-------+------------+----------+--------------+-------------+-----------+--
-----------+----------+
4 rows in set (0.06 sec)


mysql>