Chris, We have found the same thing, the more indexes you add the longer it takes to add another index. This is because at least from what I understand is the index/MYI file is recreated when you add another index.
I don't know enough about INNODB but based on what I do know you would do much better with INNODB than a MYISAM table. But I would split the table up that's the biggest slow down more than anything. Split the table on the indexed fields would be the best place to start. We have a few databases with about 4 times that amount of data/500 million rows and it's easily manageable by splitting it out. Because the odds of you ever needing every single bit of information at one time is very slim. Donny > -----Original Message----- > From: Chris Fossenier [mailto:[EMAIL PROTECTED] > Sent: Monday, January 26, 2004 12:23 PM > To: [EMAIL PROTECTED] > Subject: Index Creation Bottlenecks > > We have a large database that consists of 1 table with 400 fields. I know, > the first thing you are thinking is normalize, it's not an option right > now > and the database gets reloaded every 3 months. The table contains > marketing > data so it is not as easy to normalize as an employee database, or > helpdesk > database for example. > > We want to first focus on trying to improve our indexing performance on > this > large table. The database (once fully populated) will have 130 million > rows. > We performed testing using only 13.5 million and had the following > results: > > Indexes Time (mins) > Index1 78 > Index2 77 > Index3 78 > Index4 106 > Index5 135 > Index6 165 > Index7 192 > > You can see that the first 3 indexes seemed to be created in equal amounts > of time but after that, the indexes seemed to add 30mins to each one > (almost > to the minute). > > I believe cardinality would affect indexing but what are the odds that our > cardinality would create such an accurate graph (graph the points and > see). > > You can see by the times above that this is unnacceptable. Creating the > same > indexes on a MS SQL takes much less time and each index (aside from the > initial cluster index) takes the same amount of time. > > Thanks. > > Chris. > > > > -- > 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]
