RE: Index Creation Bottlenecks
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) > Index178 > Index277 > Index378 > Index4106 > Index5135 > Index6165 > Index7192 > > 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]
RE: Index Creation Bottlenecks
Mike, Thanks for the input. I also received this tip from Peter of the MySQL team. We'll see if it works. Chris. -Original Message- From: mos [mailto:[EMAIL PROTECTED] Sent: Monday, January 26, 2004 3:03 PM To: Chris Fossenier Cc: MySQL List Subject: Re: Index Creation Bottlenecks At 12:23 PM 1/26/2004, you wrote: >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. Chris, Try creating all indexes with a single Alter Table statement. It should be considerably faster. :) Mike -- 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]
Re: Index Creation Bottlenecks
At 12:23 PM 1/26/2004, you wrote: 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. Chris, Try creating all indexes with a single Alter Table statement. It should be considerably faster. :) Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
