Mysql Query optimisation

Question

Query 1:

SELECT  cid,
        dl
FROM    chal
WHERE   cid IN (
        SELECT  cid
        FROM    c_users
        WHERE   uid = 636587
        );

Query 2:

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

cid is primary key in chal cid and uid are indexed in c_users, cid is not unique;

Which of the above query is better?

Explain says the following

• Query 1 uses two types of index namely ALL and index_subquery

• Query 2 users two types of index namely ALL and ref

I wonder why both queries say ALL as type of index though cid is primary key in table chal.

Answer 1

These queries are not identical.

If there are 2 equal cids for a given user in c_users, the first query will return 1 record per cid, while the second one will return two records.

index_subquery is the optimization in MySQL that pushes the expression tested with IN into the IN subquery and returns TRUE on the first match.

The first query will always use chal as a leading table, while the second one can choose betwee chal and c_users and most probably will choose c_users.

You should create a composite index on c_users (uid, cid).