4

I have a table of 1 million record to join another table with 100000 records. However, there are 5 potential keys (lets assume Account Number, email address, membership number, alternative email, and ID number) in table 1 and 60 columns () in table 2 that must be used as joining keys. So, my code would be something like below:

Select * FROM tbl1 t1 join tbl2 t2 on
t1.col1 = t2.col1 OR 
t1.col1 = t2.col2 OR
t1.col1 = t2.col3 OR
...
t1.col5 = t2.col1

and so forth. The combination of 5 and 60 is huge and it kills the server. It also does not sound logical. I was thinking of putting 60 columns as row and increase the number of records for reducing the number of column. However, not yet sure if this is the best solution. Any solution that does not kill server is highly appreciated.

Note: Kindly note that each column from tb1 is to be matched against 10-15 columns in tbl2. For instance, column col1 that contains "Account Number" is joined with col1-10 which hold all potential "Account Number". col1 in no way is joined with email address or some other columns.

Improve this question
8
  • 1
    How can an email address column have the same values as a membership number column? – ypercubeᵀᴹ Dec 3 '15 at 7:42
  • 1
    I realise this may not sound very constructive, but I find it unreasonable to seek after good performance with such a crazy set-up. – Andriy M Dec 3 '15 at 7:57
  • Give us a SHOW CREATE TABLE My_Table\G for both tables. – Vérace Dec 3 '15 at 8:46
  • hypercube: that is why we have 5 fields in 1st table and 60 fields in the other one. There are 10 email addresses and 10-15 account number. They are not the same of course. – Espanta Dec 3 '15 at 18:09
  • 1
    Following your last comment to @ypercube, I think your code sample is a little misleading. It makes the impression you want to compare every column in the 5-column table against every column in the wider one, while in fact (and please correct me if I'm wrong) you probably meant to illustrate that one column in the first table is to be matched against several columns in the second, another column against several others, and so on. – Andriy M Dec 4 '15 at 7:44
6

While I think you should consider changing the setup, you could try the UNION approach.

Instead of making multiple OR joins, you can make specific queries per case, and then UNION them together. Then you have the chance to make each individual query use indexes.

So something like

Select * FROM tbl1 t1 join tbl2 t2 on
t1.col1 = t2.col1 

UNION 

Select * FROM tbl1 t1 join tbl2 t2 on
t1.col1 = t2.col2

.....

UNION 

Select * FROM tbl1 t1 join tbl2 t2 on
t1.col5 = t2.col1

and so forth.....

Doing that, I'd also consider only selecting the key from the table instead of *; and into a temporary structure. From that select the key and join and select with your data, so you can minimize the size of each individual index for the UNION-queries.

(But - you should seriously consider changing the structure in my view)

Improve this answer
8

I don't think there is a way to get this query to perform well, but the code will be easier to read if you use the IN predicate:

select * 
from tbl1 t1 
join tbl2 t2 
    on t1.col1 in (t2.col1, t2.col2, ...) 
    or ...
    or t1.col5 in (t2.col1, ... )
Improve this answer
0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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