I'm inserting data into a new table in a new database, based on a select to an existing table in an other database. The existing table has some 1500 million rows, and I am inner joining a table with about 10% of that. Both existing tables, are indexed on C5, while the the new table is indexed on C2, which is an unixtime column (I am going to do aggregates over time with the new table). This is taking horrendously long time. Its been running for over 3 hours, and the new table is just over 750 mb big, I expect it to be about 50GB when its filled up. This is also about 50% of the original table.
Is it because I'm indexing the new unixtime column?
Or could it be that I've not indexed the C8 column, that I use in my inner join? If I clustered C8 with C5, I'd probably get a faster inner join?
Here is the query:
INSERT INTO NewTable (C1, C2, C3, C4, C5, C6, C7) SELECT OldTable1.C1, OldTable1.C2, OldTable1.C3, OldTable1.C4, OldTable1.C5, OldTable1.C6, OldTable1.C7 FROM OldTable1 INNER JOIN OldTable2.C5 ON OldTable2.C5 = OldTable1.C5 WHERE OldTable2.C8 >= 70 AND OldTable2.C8<= 89;