Search code examples
mysqljpajpqlexplainsql-execution-plan

Issue with MySQL explain plan, multiple-field indices and OR clause


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:

enter image description here

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

Solution

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