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

Is cid indexed in c_users? If it's not, you're guaranteed a full table scan (aka "ALL") here.

Answer 2

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).

Answer 3

I assume you are asking which query will be faster, then as a rule of thumb, the second query will be faster. But the difference will be insignificant for tables with a small number of rows.

Answer 4

I wouldn't use joins or nested select.

I would write two sql at the application level which will be much faster as you scale.

and your select should be based on primary key on the both the tables. i.e cid