2

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?

closed as unclear what you're asking by ypercubeᵀᴹ, mustaccio, Paul White Feb 11 at 23:31

Please clarify your specific problem or add additional details to highlight exactly what you need. As it's currently written, it’s hard to tell exactly what you're asking. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.

  • You have the perfect set of index and the perfect formulation. I guess you are stuck with 11 minutes. – Rick James Aug 25 '15 at 21:05
  • What is the value of innodb_buffer_pool_size? – Rick James Aug 25 '15 at 21:08
  • 1
    Could you please show create tables' structures? – Jehad Keriaki Aug 26 '15 at 3:16
  • Voting to close as requested details (CREATE TABLE) were not provided. – ypercubeᵀᴹ Feb 11 at 9:12
0
  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.

  • On the contrary, it says Using index for both tables, implying that there are composite indexes on both tables. – Rick James Aug 25 '15 at 21:02
  • #2 -- he clearly has that in both tables, since it says Using index. There is no full scan of BB; it's even caching the index in the Join buffer. – Rick James Aug 25 '15 at 21:07
0

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.

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