Re: Updating 100 million record tables

[ed ziffel]

RJ,

Great stuff! Going to take me a day to two to go through it due to time constraints.

But off the top:
1. Will increase buffer size as recommended.
2. c4 is indexed
3. c4 is a varchar, names of widgets as it were.
4. Will check data specifications: Good call.
5. EXPLAIN: Never used EXPLAIN before. Looks like a good time to tune up some skills.
6. Uploaded file sizes were 26gig and 16gig, not that it matters with respect to your point.
7. Report has to do with accounting compliance issues where it is necessary to forensically match production aspects to shipped product. Some mandatory "Put a brick to sleep" action here.
8. Values vary. Ran query to determine unique instances for permutation of c1+c2+c3 by c4 something like 1.2 million results.
9. Growing chorus for splitting the tables. Have issues with cutoffs on that though. For instance: how to do? Absolutely going to have 10% or more cases where if cutoffs are by date, then will have to use multiple tables to find results as a widget made in Y1 may be shipped in Y2 or Y3 or even Y(n).
10. The loop: How do you set up a loop just using MySQL. Or are you assuming that the loop is set up in another server side language? Setting it up using php was what I originally had in mind. Again, c4 is a varchar. What issues does that cause? Added an auto increment primary key to tables is that helps.
11. Great advise on not locking up the server. This is going to be a very useful tip going forward. On a scale of 1 to 10 figure a 10. New to most of this. Saw server issues coming but wasn't in my face so was on the back burner. This is going to save a ton of future grief. For the moment however, none of this is on a production server. All local so can tweak and crash at will. Getting to be real good at the format and reinstall.

Thank you for the seriously great help. Moving all into my permanent notes.
Will get back to you when have the other items addressed.

Ed