0

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!

0

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

  • We tried it. But we have the same behavior if we put the same CHARSET and COLLATION. – Philiz Jun 17 at 15:15
  • @Philiz - Please update your Question with consistent charset and collation, plus rerun the EXPLAIN for that case. – Rick James Jun 17 at 16:13
  • 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. – Philiz Jun 17 at 21:54
0

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)
  • This does not fix the problem. The query is still very slow. – Philiz Jun 17 at 13:43

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.