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.
> -----Original Message-----
> From: Remigiusz Sokołowski [mailto:rems@stripped]
> Sent: Friday, August 20, 2004 1:12 AM
> To: matt ryan; mysql@stripped
> 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: