Why is mysql ignoring INDEX on JOIN query-CodePudding

I have 2 tables postsTable and groupsTable. I have posted structure and indexes of both below.

My question is, in the query below, mysql should use "index nCode" of groupsTable. But its completely ignoring it, even though its listing it as a possible index.

The index of postsTable is followed as expected.

What can I do here to remedy this? Thanks

create table postsTable 
(pid int(18) auto_increment not null primary key,
userID int(10),
stat int(10),
mainID int(10),
title varchar(256),
INDEX( userID, stat, mainID )
);

create index postPStat on postsTable (stat, mainID);


create table groupsTable
(cid int(10) auto_increment not null primary key,
nCode int(10),
cStat (char2) default 'y',
aCode varchar(256),
groupName varchar(256),
INDEX(nCode, cStat, aCode )
);

Query is this:

select p.pid, p.title, t.groupName 
from postsTable as p
left join groupsTable as t 
on p.stat = t.nCode
where 
p.stat = t.nCode 
and p.mainID=0 
and t.cStat='y' 
group by p.pid

Explain is this:

2 in array
Array
(
    [0] => Array
        (
            [id] => 1
            [select_type] => SIMPLE
            [table] => t
            [partitions] => 
            [type] => system
            [possible_keys] => nCode
            [key] => 
            [key_len] => 
            [ref] => 
            [rows] => 1
            [filtered] => 100.00
            [Extra] => Using filesort
        )

    [1] => Array
        (
            [id] => 1
            [select_type] => SIMPLE
            [table] => p
            [partitions] => 
            [type] => ref
            [possible_keys] => PRIMARY,id,id_2,postPStat
            [key] => postPStat
            [key_len] => 16
            [ref] => const,const
            [rows] => 1
            [filtered] => 100.00
            [Extra] => Using index condition
        )

)

CodePudding user response：

Your current query doesn't seem to need GROUP BY, and also the join logic most likely has a problem. Consider this version:

SELECT p.pid, p.title, t.groupName 
FROM postsTable p
LEFT JOIN groupsTable t 
    ON p.stat = t.nCode AND
       t.cStat = 'y'
WHERE p.mainID = 0;

The index you want here is on the groupsTable:

CREATE INDEX idx ON groupsTable(nCode, cStat, groupName);

Note that this index is different from (nCode, cStat, aCode), which is what you currently have. Your index does not cover the select clause, and therefore doesn't cover the entire query.