MySQL Forums
Forum List  »  General

Updating 100 million record tables
Posted by: ed ziffel
Date: November 26, 2011 02:58PM

Have two large tables: t1 has 105 + million records. t2 has 43 + million records.

Have query:
UPDATE t1, t2
set t1.c1=t2.c1, t1.c2=t2.c2, t1.c3=t2.c3
where t1.c4=t2.c4

Of course using different columns but the above query is the exact structure. This is of course taking forever as in after hours have not seen a result and just interrupted the execution of the query.

Q1. Is it correct to figure that done this way, SQL is looking at (rounding a bit to keep it simple) 100 million X 40 million X 4 (number or updates evaluated individually plus evaluating the where clause) records trying to get this query executed?

Q2. Given that updating the columns in t1 from t2 is a must, what might be a better approach to this problem? IE: make many smaller tables and do it that way? ????

Q3. Using MySQL 5.1 here. According to the manual, and what I get when I try, because the query uses two tables to update from, a limit clause is not allowed. Is there some other way to narrow down the query range so that you can see if it is working properly to begin with. Pretty sure the query is ok, but would still like to able to to a quick check to proof.

Have tried to limit range by date in t1, just use one column at a time to update but even that is 100 million X 40 million X 2 is a pretty big number. Have indexed the relevant fields t1.c4, t2.c4 and even tried indexing t1.c1 and t2.c1, the columns being updated even though that really doesn't make sense, as they have to be done one at a time anyway. What should I try to get this done



Edited 1 time(s). Last edit at 11/26/2011 03:01PM by ed ziffel.

Options: ReplyQuote

Written By
Updating 100 million record tables
November 26, 2011 02:58PM

Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.