Updating 100 million record tables

[ed ziffel]

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

Thanks

Ed



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