MySQL Forums
Forum List  »  InnoDB

Re: Disable single index instead of all indexes
Posted by: Rick James
Date: February 20, 2016 12:00AM

"recreating index is taking long time on millions of records" -- Yes, it takes a long time -- whether you "turn off the index" or "recreate the index".

In fact, once you turn off the index, the only way to have the index again is to rebuild it. So, we have to focus on either leaving it in place, or rebuilding it.

* Leaving secondary key(s) in place means that some effort is happening in the "Change Buffer". This is a part of the buffer_pool that is used for collecting pending index updates. By delaying the update of the index, less I/O is done. (How much less -- that depends on a lot of factors and numbers you have not yet provided.) For adding a million rows and if you have a big enough buffer_pool (hence change buffer), the updating of the secondary index(es) should be virtually transparent. The INSERTs will finish, then the indexes will be updated over the next minutes or hours. Meanwhile, the indexes are fully usable.

* Rebuilding a secondary index in 5.6.7 and later is also mostly "transparent" in that it does most of the work in the background, while leaving the table accessible.

I recommend loading your million rows into a temp table, then copying them into the main table 1000 at a time. (Each batch should be a transaction.) That way the there will be only a slight chance of deadlock, etc., during each transaction. Meanwhile, the Change Buffer will take care of the secondary keys.

If Kettle gets in the way, well, I have choice words for 3rd party packages.

Options: ReplyQuote

Written By
February 12, 2016 01:26AM
Re: Disable single index instead of all indexes
February 20, 2016 12:00AM

Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.