Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Lumbago
Norsk Yak Master
3271 Posts |
 Posted - 2009-03-11 : 06:30:02
|
| Hi,first of all I should mention that this is actually an issue regarding MySQL but the problem is really about indexing strategy so I hope you don't mind.The thing is that I have this table with about 360 mill rows of call records, and I need to change one of the columns because it's bound to exceed the maximum value for the datatype it's using. As you might understand this is a heavily used table, and to limit downtime to an absolute minimum I have created a new table with the correct datatypes and copied all of the data (except from the last few days) over to this new table, without indexes. My plan is to just switch these two tables over my simply renaming them when all is ready. Now here comes the real problem:I need to add 3 indexes to this new table, and when I try to do this it runs for a few hours until the server starts slowing down to a state where the application that inserts the data starts buffering because the inserts don't happen fast enough. So basically I wanted to ask you if you know of any kind of strategy that will let me index faster or index the data in chunks or somethingI have been considering partitioning the new table but that has some side effects that will require a considerable amount of planning...- Lumbago |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
|
|
RickD
Slow But Sure Yak Herding Master
3608 Posts |
Posted - 2009-03-11 : 06:47:44
|
| At that size, I would certainly think about partitioning (not sure how MySQL handles this?!?). Also, another thing with SQL Server is creating indexes ONLINE, again, i'm not sure if there is an equivalent command in MySQL. How relevant is the data? Do you need it all or can you siphon some of it off into an archive table as this is another choice? |
 |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
|
|
Lumbago
Norsk Yak Master
3271 Posts |
Posted - 2009-03-11 : 08:28:15
|
| Very interesting reads both of them! But unfortunately not relevant I believe... The table I need to index already has a primary key index (same as clustered index in sql server)...I need to make 3 additional nonclustered indexes and this is what's causing my headache. Maybe partitioning is my only option..?- Lumbago |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2009-03-11 : 09:03:10
|
| Since you have already implemented Clustered index,Adding non-clustered index won't be such big issue I guess.Why don't you add 1 non-cl index at a time instead of adding 3 in same time.?Also do in offline hours(If you have)so you don't have concurrency error. |
|
|
|
Lumbago
Norsk Yak Master
3271 Posts |
Posted - 2009-03-11 : 09:22:49
|
quote:
Adding non-clustered index won't be such big issue I guess
Doing stuff on a 360 mill row table is a big issue almost regardless of what you do, and unfortunately it's even worse this being MySQL. And there are only slow hours, no offline hours...- Lumbago |
|
|
|
|
|
|
|
|
Indexing on large table