Matt, I've been reading this thread for a while and at this point, I would say that you would need to provide the table structures and queries that you are running.
For example, we have one table that has 8 billion rows in it and it close to 100 gigs and we can hammer it all day long without any problems. It really depends on how you are doing things. But as far as you mentioning about mysql not using multiple indexes, it does. You just have to create an index on multiple fields at one time. I've got tables with 10 fields in one index, now the trick with mysql is that you must use all top 10 fields in your where clause for mysql to really take advantage of the index. But I would definitely send the list your table structure with your indexes and some of your selects and inserts. You can always change the names of things if you don't want people to know the names of everything. Just my 2 cents. Donny > -----Original Message----- > From: matt ryan [mailto:[EMAIL PROTECTED] > Sent: Thursday, July 15, 2004 9:32 AM > To: [EMAIL PROTECTED] > Subject: Re: Mysql growing pains, 4 days to create index on one table! > > Tim Brody wrote: > > >You may find that the 'dic' KEY isn't necessary, as it's the first part > of > >your PRIMARY KEY. > > > > > >I've found better performance for multi-column keys by putting the > columns > >in order of least variance first, e.g. for a list of dates: > >1979-04-23 > >1979-07-15 > >1980-02-04 > >1980-06-04 > >You want a key on (YEAR-MONTH-DAY) > > > >If you can you could put the index/data on different disks - not sure how > >you would do that in Windows (INDEX DIRECTORY option for CREATE TABLE?). > > > >You should definitely put the binary log file on another disk, but again > not > >something I've used. > > > >I've found MySQL to be a royal pain working with multi-GB tables (my > biggest > >is 12GB+13GB index). I've learnt that MySQL is a bit like a confused > puppy - > >it doesn't know why it wet the floor, but it expects you to know that > pained > >expression means you need to move it somewhere else ... > > > > > > > I need the DIC in the key to keep the record unique, I have thousands > with everything identical except the DIC. > > I was confused on the multi key index issue, I thought it would seek > faster if I put the most unique field up front, which I do on most > tables, I did not on this one though. I have one large raid array now, > so I cant split the data, or put the binary log on another disk. > > I found mysql was great up to about 3 gig, then everything hit the > brakes and got really really really slow > > I'm scared of joines, every time I do a join in mysql on indexed fields > in mysql, the performance is horrible, because the where clause is not a > field that's in the join, performance is poopy > > I wish mysql could use multiple indexes like oracle, to narrow down the > results, I've got some simple queries that take hours due to single > index use, but every query field is indexed. > > > > -- > 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]
