Geek Answers Handbook

MySQL correct way to add a column from one table to another

I have a large table (~ 10 million records) that contains several keys in other, smaller tables. The keys are unique in each of the smaller tables, but not in the large. I would like to add a column to a large table from one of the smaller tables based on key matching, but I'm not sure about the correct way to execute it. I have a solution that works, but it takes quite a lot of time (it seems that this may be inevitable), and, as a rule, it does not seem that this is the best way to do this. Here is what I have:

CREATE TABLE new_big_table LIKE big_table;
ALTER TABLE new_big_table ADD(new_column TINYINT NOT NULL);
INSERT INTO new_big_table SELECT big_table.*, smaller_table.my_column
  FROM big_table JOIN smaller_table ON big_table.key1 = smaller_table.key1
  AND big_table.key2 = smaller_table.key2;

It does its job, but it actually smells like I'm doing everything wrong. It seems that, at a minimum, I do not need to duplicate the table to do this. Is there an even more direct (and more efficient?) Way to do this?

It may be worth mentioning that this is for a personal, hobby project at home, so I am free to deflate all the resources of the machine (since I am the only one who uses it). Thus, if there are any simple performance tuning tips for such things, I would appreciate them (I am experimenting with this on an Amazon EC2 instance, as it should be much faster and have more memory than my personal desktop).

+5
source share
1 answer

?

alter table big_table add new_column tinyint;

update  big_table bt
join    smaller_table st
on      bt.key1 = st.key1
        and bt.key2 = st.key2
set     bt.new_column = st.my_column;

alter table big_table modify new_column tinyint not null;
+5

More articles:


All Articles