Matt, I know you have gotten a lot of recommendations, I have 3 for you that I don't think anybody has mentioned.
1. Try a merge table. We had 1 table with about 750 million rows in it, and every once in a while we would need to do something crazy to it and it would be locked up for hours. We decided to break it up into 10 different tables, based on the last digit of one of the fields. So whenever we did inserts they went directly into one of the 10 tables, but whenever we did a select if we didn't know which table to search in we used the merge table and it was just fine. This definitely helped us. 2. Get MySQL to come to your office and have them do some consulting. Or have them do it online, personally I recommend onsite consulting. It's not very expensive especially if you can solve your problem. We have MySQL coming out to our office in right over a week from now and the consultant is staying for 3 days. Do I really need the consultant to come in, not really, but if they can tweak, improve, or help us with just a few things, it's worth every penny. For example, two weeks ago, I was about to buy another quad Xeon 3.4ghz machine and before I ordered it I contacted our consultant just to ask their opinion and he told me to go with a 64bit machine. So now I have a quad AMD 64 2.2 with 32gigs of memory coming in next Wednesday for me to play with. You can find the information on the mysql site somewhere. 3. The last option, is a cheaper option than #2, but it can sometimes work just as well. Find the next time mysql will be holding a training class in your area, and go to the class. Then while you are in the class have the instructor look at your tables, queries, etc... and see what they think. Now, I've been to 3 of them now, some of the instructors can baffle you with how much they know. And some of them are more book taught. As one instructor asked me, if you can write a 12 table join without looking at your table structure, why are you here? My response, "I have this problem, want to take a look." My problem was resolved by the end of the session. So it's up to you, but personally I recommend #2. You can have them come in and you can have a list of 100 questions, and go right down the list and have them answer everyone of them. I may even have them setup my new little toy when he comes in, you never know. Donny > -----Original Message----- > From: Remigiusz Sokołowski [mailto:[EMAIL PROTECTED] > Sent: Friday, August 20, 2004 1:12 AM > To: matt ryan; [EMAIL PROTECTED] > Subject: Re: 1 day 28 min insert > > matt ryan wrote: > > > The table is 9 gig, and the index is 8 gig > > > > unfortunately the primary unique key is almost every column, if I were > > to make it one using concat, it would be huge. > > > > I tried making those fields a hash, but it did not work, I had > > duplicate hashes for non duplicate records!! > > > > Matt > > > If I well understood, You have in Your index almost all data, You have > in Your table? > Why not add field for unique key (auto increment if You want less work)? > It reduces size of Your primary index and thus speed up working with it! > > Best regards > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: > http://lists.mysql.com/[EMAIL PROTECTED] > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
