0

I have two tables with several columns, and I want to join these tables using an outer join. However, it takes forever. I just think I made a mistake or maybe there are other solutions?

create table table_1 (
  sn int(11) unsigned NOT NULL AUTO_INCREMENT PRIMARY KEY,
  column1 int(6) NOT NULL,
  column3 int(1) NOT NULL,
  column4 date NOT NULL,
  column5 char(2) NOT NULL,
  column6 int(6) NOT NULL,
  ...
  ...
  column15 int(1) NOT NULL
)

create table table_2 (
sn int(11) unsigned NOT NULL AUTO_INCREMENT PRIMARY KEY,
column1 int(6) NOT NULL,
column3 int(1) NOT NULL,
column4 date NOT NULL,
column5 char(2) NOT NULL,
column6 int(6) NOT NULL,
...
...
column8 int(1) NOT NULL
)

I've created an index on these 2 table with column1, column2, column3, column4, column5 and column6.

My problem is I want to right join these two tables with:

table_1.column1 = table_2.column1 AND
table_1.column2 = table_2.column2 AND
table_1.column3 = table_2.column3 AND
table_1.column4 = table_2.column4 AND
table_1.column5 = table_2.column5 AND
table_1.column6 = table_2.column6

When I execute this query, it takes a very long time. Is there any solution to make this query faster?

Table_1 size: 3 GB and table_2 size: 10 GB. Thanks.

plain jane
  • 1,009
  • 1
  • 8
  • 19
korn3lius
  • 1
  • Did you take a look at `EXPLAIN `? If you didn't do it and post result in a textual form in your question along with `SHOW INDEX FROM table_1` and `SHOW INDEX FROM table_2`. – peterm Aug 19 '13 at 02:30
  • You want select all columns or check only existence in first table (check null values)? It look like move data from second to first table (depends on sizes and structure). – slavoo Aug 19 '13 at 10:21
  • The join query was never ends. so I quit after I wait for 1 week, and then I concat the 6 column and use HASH to make a new variable. After that, make index of that HASH, and join them together. – korn3lius Aug 26 '13 at 12:52

0 Answers0