MySQL Forums :: General :: Updating 100 million record tables


Advanced Search

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

Thanks

Ed



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

Options: ReplyQuote


Subject Written By Posted
Updating 100 million record tables ed ziffel 11/26/2011 02:58PM
Re: Updating 100 million record tables Rick James 11/27/2011 03:16PM
Re: Updating 100 million record tables ed ziffel 11/27/2011 06:42PM
Re: Updating 100 million record tables Rick James 11/28/2011 07:13PM
Re: Updating 100 million record tables ed ziffel 11/29/2011 09:22AM
Re: Updating 100 million record tables Rick James 11/30/2011 09:40AM
Re: Updating 100 million record tables ed ziffel 12/01/2011 11:15AM
Re: Updating 100 million record tables ed ziffel 12/05/2011 11:11AM
Re: Updating 100 million record tables Rick James 12/06/2011 08:39PM


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.