I have an explain plan result that I don't understand. I have the following table that hold messages sent from senders
and to recipients
.
CREATE TABLE `message` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`message_read` bit(1) NOT NULL,
`send_date` datetime NOT NULL,
`text` varchar(500) NOT NULL,
`version` int(11) DEFAULT NULL,
`recipient` bigint(20) NOT NULL,
`sender` bigint(20) NOT NULL,
PRIMARY KEY (`id`),
KEY `index4` (`recipient`,`sender`) USING BTREE,
KEY `index5` (`sender`,`recipient`) USING BTREE,
CONSTRAINT `FK_a3km2kv42i1xu571ta911f9dc` FOREIGN KEY (`sender`) REFERENCES `member` (`id`),
CONSTRAINT `FK_hn9roqyj131hnul5fuwgwlv9e` FOREIGN KEY (`recipient`) REFERENCES `member` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=33 DEFAULT CHARSET=utf8;
Here is my explain plan:
explain select *
from
message m
where
(
m.sender = 1
and m.recipient= 2
)
or (
m.recipient = 1
and m.sender= 2
)
Here is the result from my explain plan:
I am not sure why the key
field is null
(the possible_keys
does mention the indices I want to use).
How can I make sure the index4
and index5
indices are used?
edit 1:
I have inserted a number of rows into the table. The following explain:
explain select *
from
message m FORCE INDEX(index4)
where
(
m.sender = 1
and m.recipient= 2
)
or (
m.sender= 2
and m.recipient = 1
)
now yields the following results:
1 SIMPLE m range index4 index4 16 32 Using where
In order to force the use of an index, you can use FORCE INDEX...
SELECT *
FROM message FORCE INDEX(index4)
WHERE ((recipient = 2 AND sender = 1) OR (recipient=1 AND sender = 2));
On a table of 32 rows, this is probably less efficient than a full table scan.