CODEWITHSUNDEEP
mysqlsqlperformance
Dazed and Confused
Dazed and Confused

Reputation: 81

UNION ALL taking days. Speeding up?

I've got two tables: one is about 200 million rows, the other about 100 million. I need to join them together and select about 3/4 of their rows and 1/6 of their columns, so have created a new table, and am inserting into it using:

INSERT INTO NEWTABLE 
SELECT COLUMN1, COLUMN2, COLUMN3, COLUMN4
FROM TABLE1
UNION ALL 
SELECT COLUMN1, COLUMN2, COLUMN3, COLUMN4
FROM TABLE2 
LOCK IN SHARE MODE;

This is taking a while. Days in fact.

Am I doing this the fastest way?

My feeling is that the query is not the main issue - it's the copying of both source tables to a temporary table that takes the time. How do I prove this? And if that's the case, can I circumvent it?

Thank you!

Upvotes: 1

Views: 323

Answers (2)

vivek
vivek

Reputation: 11

Removing UNION ALL is enough. Despite, if you are having problems, kindly post time difference here.

  • Disable indexes on NEWTABLE if it is having
  • Run two INSERT DDLs.
  • Enable indexing

Upvotes: 0

Jodrell
Jodrell

Reputation: 35706

Why union all?

INSERT INTO NEWTABLE
SELECT COLUMN1, COLUMN2, COLUMN3, COLUMN4
FROM TABLE1
LOCK IN SHARE MODE;

INSERT INTO NEWTABLE
SELECT COLUMN1, COLUMN2, COLUMN3, COLUMN4
FROM TABLE2 
LOCK IN SHARE MODE;

achieves the same.

Upvotes: 5

Related Questions