[Mysql] Using index on join

Question

Using EXPLAIN for the query below shows

SELECT cha.cid AS cid,
       cha.data AS dl
FROM cha, c_users
WHERE uid = 808
AND cha.cid = c_users.cid;

1. it does a full scan on cha table
2. uses the multi column index(cid,uid) from c_users.

Why doesn't it not use the primary key index from cha and rather does a full table scan. Is there a better way to optimise the query/table.

Edit: cha.cid is my primary key. Added after the comment below.

Answer 1

Wouldn't...

SELECT cha.cid AS cid,
       cha.data AS dl
    FROM cha INNER JOIN c_users ON cha.cid = c_users.cid
    WHERE uid = 808;

be more idiomatic?

(Edited to take account on knittl's comment).

Answer 2

Create an index for uid column.

When you ask question about performance, output of EXPLAIN and CREATE TABLE statements makes things much easier for helpers. Please add them next time.

Answer 3

In a normal BTREE, and an index on (cid,uid), a scan will be needed if you don't specify cid and only search for uid. An index on (uid,cid) (notice the order) would / could help.

This is by no means a guarantee it will be used, MySQL can guess that a full scan might be quicker (continuous read) if a certain index / join most likely will require a certain large percentage of the index to be used, and other reasons. You can check with FORCE INDEX if using the key compared to a scan is actually any quicker or not (disable query cache on a testserver before testing this).

Answer 4

Since cha doesn't have a covering index available for this query it has to access the data pages anyway to retrieve some of the data it needs. Based on table statistics, the optimizer has decided that the IO time of first scanning the index and then jumping to the data pages is like to be worse than just scanning the table.