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?
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 (  => 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 )  => 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 ) )
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
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.