1

I know how to JOIN two tables as create a new table out of the results, using CREATE TABLE result_table AS ( SELECT * FROM tableA JOIN tableB ).

I forgot to include some columns from the JOIN in the result_table. Is it possible to merge some of columns from tableA into result_table? Is there a performance improvement over simply dropping and re-creating result_table again?

2

You are better off dropping and recreating. Why ?

ANALYSIS

Let's take the hypothetical example from your question

CREATE TABLE result_table AS ( SELECT * FROM tableA JOIN tableB );

This is literally a pure Cartesian Product.

Let's say tableA has 2,000 rows and tableB has 5,000. A Cartesian product would result in result_table having1,000,000 rows.

Now let's say you added the column col_1 to tableA.

If you had to fill in tableA into result_table, what would the join look like ?

First, you would have to graft col_1 into result_table.

ALTER TABLE result_table ADD COLUMN col_1;

That's a pass through 1,000,000 (1 million) rows to inject a column.

Then, you have to perform a join of the ugliest kind

UPDATE result_table JOIN tableA
ON result_table.column_key_from_table JOIN tableA.column_key_from_table;

Remember I said joining 2,000 rows to 5,000 rows made result_table 1,000,000 rows ?

To perform the UPDATE JOIN, you would have to create a temp table with 2,000,000,000 rows. Yes, 2 billion rows (1,000,000 times 2,000). The temp table has to be created. Then, you have to perform pass through the 2 billion rows to populate the col_1 column.

CONCLUSION

I think you want to be finished working with the table so you can have time to raise a family, collect your pension, etc.. So, I'd go with the dropping and recreating the table.

  • Agreed to @RolandoMySQLDBA as Adding a new column will itself take time and then a update, so it is better to drop and create the table again. – Abdul Manaf Jul 22 '14 at 16:05
  • This analysis is spot on in the general case. However, if the tableA has the FK the points to the PK in tableB, then result_table would only have as many rows as tableA. In that case, is it correct that there's not much of a difference between UPDATE JOIN and recreating the table? – Heisenberg Jul 22 '14 at 17:05
  • You still have the issue of grafting in the missing columns, which will take some additional time, even if the JOIN results has the same number of rows. Still results in doing two steps (ALTER table and UPDATE JOIN). Drop and recreate is a cleaner approach because the appearance of the new column and its population is done in a single pass and single command. – RolandoMySQLDBA Jul 22 '14 at 17:17

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.