Vryjdfkk

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?

1. Create the result table, my_diff, without index, and when it is
   populated, add the desired index.


2. Make sure you have an index on (name, type) in both tables. i.e. a composite index


3. 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.

Create the temp table as follows

#

# Make Temp Table

#

CREATE TABLE mydiff SELECT name,type FROM AA WHERE 1=2;

ALTER TABLE mydiff ADD COLUMN diffcount INT DEFAULT 1;

ALTER TABLE mydiff ADD PRIMARY KEY (user,type);

#

# Load Unique Keys From Table AA

#

INSERT IGNORE INTO mydiff (user,type) SELECT user,type FROM AA;

#

# Load Keys From Table BB

#

INSERT INTO mydiff (user,type) SELECT user,type FROM BB

ON DUPLICATE KEY UPDATE diffcount=diffcount+1;

Then, count the missing keys

SELECT IFNULL(diffcount,'Total') diffcount,COUNT(1) rowcount

FROM mydiff GROUP BY diffcount WITH ROLLUP;

The output for diffcount of 1 shows how many (user,type) are not represented

To see the unused (user,type) records, run this

SELECT user,type FROM mydiff WHERE diffcount = 1;

GIVE IT A TRY !!!

It's better to insert 4 Million Rows than join 3,311,651,902 (396178 X 8359) rows.