jaymc Posted August 18, 2008 Share Posted August 18, 2008 Is there anything you can do to speed up the time it takes to add or drop an index on a tale which has millions of rows It can take up to 20 minutes for some of my tables? Quote Link to comment Share on other sites More sharing options...
Hooker Posted August 18, 2008 Share Posted August 18, 2008 alot of the time when i'm working with hundreds of millions of rows it's alot faster to just drop the table, re-create it with the nessisary index's and then re-import the data. Quote Link to comment Share on other sites More sharing options...
jaymc Posted August 18, 2008 Author Share Posted August 18, 2008 Nice idea, does any one else know of any other solutions? Quote Link to comment Share on other sites More sharing options...
fenway Posted August 20, 2008 Share Posted August 20, 2008 Nice idea, does any one else know of any other solutions? Well, non-unique indexes are usually faster... for dropping, i'm not sure, though 5.1 does have some on-line operations. For adding, there is an implicit sort done from a temporary table, so look into increasing myisam_sort_buffer_size (assuming myisam tables). Quote Link to comment Share on other sites More sharing options...
jaymc Posted August 20, 2008 Author Share Posted August 20, 2008 They are Innodb I was offline for 40 minutes just to add an index on a table, mysql must have a solution for this? Quote Link to comment Share on other sites More sharing options...
fenway Posted August 21, 2008 Share Posted August 21, 2008 They are Innodb I was offline for 40 minutes just to add an index on a table, mysql must have a solution for this? Sorry... don't have much experience with that. Quote Link to comment Share on other sites More sharing options...
jaymc Posted August 23, 2008 Author Share Posted August 23, 2008 No problem Can anyone else help? Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.