On MySQL 5.7 I want to get the records from table 1 that are not present in table 2.
I'm using this simple LEFT JOIN query that is very slow (more than 2 minutes), I don't know why.
SELECT * FROM t1 LEFT JOIN t2 ON t1.name = t2.name WHERE t2.name is null
When I do an explain of the query I get this result
id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 NULL index NULL NAME_UNIQUE 49 NULL 66387 100.00 Using index 1 SIMPLE t2 NULL index NULL NAEM_UNIQUE 37 NULL 2275410 10.00 Using where; Using index; Using join buffer (Block Nested Loop)
I have a unique index on both columns "name".
Here are the tables definitions:
CREATE TABLE `t1` ( `ID` int(10) unsigned NOT NULL AUTO_INCREMENT, `NAME` char(12) CHARACTER SET utf8 DEFAULT NULL, PRIMARY KEY (`ID`), UNIQUE KEY `NAME_UNIQUE` (`NAME`) ) ENGINE=InnoDB AUTO_INCREMENT=50053 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; CREATE TABLE `t2` ( `ID` int(10) unsigned NOT NULL AUTO_INCREMENT, `NAME` char(12) DEFAULT NULL, PRIMARY KEY (`ID`), UNIQUE KEY `NAME_UNIQUE` (`NAME`) ) ENGINE=InnoDB AUTO_INCREMENT=2049985 DEFAULT CHARSET=utf8mb4;
I think it's something related to the use of "Using join buffer (Block nested loop)" but I tried to switch off with no effect:
SET SESSION optimizer_switch='block_nested_loop=off';
Any ideas to make this query perform much better? Thanks!