MySQL compare values of two large tables

Question

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!

Answer 1

Looks like a mismatch between the CHARSET on the two tables. Try setting the same CHARSET and COLLATION on both tables.

• Philiz: We tried it. But we have the same behavior if we put the same CHARSET and COLLATION.
• Rick James: @Philiz - Please update your Question with consistent charset and collation, plus rerun the EXPLAIN for that case.
• Philiz: Ok we did a bad check on the charset, sorry for this. Setting the same CHARSET AND COLLATION fixed the problem! Thank you for your help.

Answer 2

Apart from different charset, left join might be doing extra work because it computes values from t1 that join with possibly many rows from t2, and that will be discarded anyway due to the where clause. Try NOT IN instead:

SELECT * FROM t1
WHERE t1.name NOT IN
(SELECT t2.name FROM t2)

• Philiz: This does not fix the problem. The query is still very slow.