I have the following table :
CREATE TABLE `note` (
`id` char(36) NOT NULL,
`body` text CHARACTER SET utf8mb4,
`title` varchar(255) CHARACTER SET utf8mb4 DEFAULT NULL,
`preview` text CHARACTER SET utf8mb4,
`created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
`updated` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`user` varchar(34) DEFAULT NULL,
`conversation` varchar(34) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
And I performed the following query :
explain select * from note where user='u:ynul5luymmnlys0u' and conversation is not null order by updated desc;
In the output the key column had value NULL
which is expected as there is not index on user column.
So i added an index :
create index user_idx on note (user);
And performed the same query the output still had NULL in key column ??
Although in possible_keys it did show user_idx. Dont know why it didnt use the index on user column.
Please help me in understanding the above behavior.