Index question

[Michael Widenius]

Hi!

>>>>> "Willem" == Willem Bison <Attic@stripped> writes:

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

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

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

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

Willem> - Why is no index on PERSON used ?

As the person table is read first, then one can't use the the
cid,delete index to lookup the persion.

You can of course force clan to be read second with:

select person.nshort from clan straight_join clan where person.cid=clan.id and
person.deleted='N';

but I don't think this will be faster.

Willem> - Was I right in adding a multiple index (cid,deleted) for queries where
Willem> both 'cid' and 'deleted' are tested ?

If person would be read after clan, the above would be ok.

In your case it's probably better to add an index:

(delete,cid).

Regards,
Monty