Description:
optimizer didn`t use unique index and go through full table scan
when left join or join
on unique column = primary key in the other table
How to repeat:
CREATE DATABASE DB1;
USE DB1;
CREATE TABLE `tb2` (
`ID_` bigint(20) unsigned NOT NULL,
`VAL` double(10,4) NOT NULL DEFAULT '0.0000',
PRIMARY KEY (`ID_`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
CREATE TABLE `tb3` (
`ID_` bigint(20) unsigned NOT NULL,
`INVO_NO` bigint(20) unsigned NOT NULL,
`disc` double(10,4) NOT NULL DEFAULT '0.0000',
PRIMARY KEY (`ID_`),
UNIQUE KEY `INVO_NOUN` (`INVO_NO`) USING BTREE,
CONSTRAINT `INVO` FOREIGN KEY (`INVO_NO`) REFERENCES `tb2` (`ID_`) ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
INSERT INTO `tb2` (`ID_`,`VAL`) VALUES (10,100.0000);
INSERT INTO `tb2` (`ID_`,`VAL`) VALUES (20,50.0000);
INSERT INTO `tb2` (`ID_`,`VAL`) VALUES (30,70.0000);
INSERT INTO `tb3` (`ID_`,`INVO_NO`,`disc`) VALUES (10,20,100.0000);
INSERT INTO `tb3` (`ID_`,`INVO_NO`,`disc`) VALUES (11,30,200.0000);
INSERT INTO `tb3` (`ID_`,`INVO_NO`,`disc`) VALUES (12,10,300.0000);
EXPLAIN SELECT
tb2.ID_,TB2.VAL,
tb3.ID_,tb3.INVO_NO,tb3.disc
FROM db1.tb3
LEFT JOIN tb2 ON tb3.INVO_NO=tb2.ID_;
EXPLAIN SELECT
tb2.ID_,TB2.VAL,
tb3.ID_,tb3.INVO_NO,tb3.disc
FROM db1.tb3
JOIN tb2 ON tb3.INVO_NO=tb2.ID_;
