I'm trying to find the diff of two tables using a left join.
My query is as follows:
CREATE TABLE my_diff (INDEX my_index (name, type)) SELECT AA.name, AA.type FROM AA LEFT JOIN BB USING (name, type) WHERE BB.type IS NULL;
- Table AA is sized: ~400K records and has PRIMARY KEY on all columns as well as an INDEX on each column
- Table BB is sized: ~8K records and has a composite INDEX on both columns: my_index (name, type)
id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE AA index NULL PRIMARY 383 NULL 396178 100.00 Using index 1 SIMPLE BB index NULL my_index 104 NULL 8359 100.00 Using where; Not exists; Using index; Using join buffer (Block Nested Loop)
The query takes a mind-boggling 11 min to finish.
I have tried various options of composite and single column indexes / primary keys, tried forcing the keys/indexes on the select and the join, but to no avail.
What can I do to improve this?