Finding differences between two tables takes too long 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)


EXPLAIN EXTENDED:

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?
Create the result table, my_diff, without index, and when it is
populated, add the desired index.
Make sure you have an index on (name, type) in both tables. i.e. a composite index
It is not a good idea to have primary key on all fields. This is especially true if you are using InnoDB engine. Instead, alter the table to add an integer auto increment field as primary key, and if needed, add a composite unique index on the fields you want to be unique.


From your explain result, it looks like you don't have the index on (name, type), so for each row in AA, it will do a full  scan to BB. This is one enough reason to make the query slow.