MySQL Forums
Forum List  »  InnoDB

Re: Disable single index instead of all indexes
Posted by: Rick James
Date: March 15, 2016 08:16PM

I would never turn off any indexes of the huge Datamart.

Instead, I would transfer rows from the staging table to the Datamart in chunks. This would minimize the impact on everything else.

I might pick 1000 rows at a time. If the staging table has a PRIMARY or UNIQUE key, I would use that for walking through the table. I would not use OFFSET; it is costly since all the skipped rows need to be actively skipped over.

More on chunking:
http://mysql.rjweb.org/doc.php/deletebig#deleting_in_chunks
(It is aimed at DELETE, but it can be adapted to SELECT for your situation.)

It may be possible to read the Staging table in some particular order. If that order matches one of Datamart's indexes, then the updates to _that_ index will be more efficient. (Other indexes are likely to be randomly updated.)

As I implied before, you can DROP and reCREATE an index with somewhat minimal affect, if you are using 5.6.7 or later. However, if the index is needed for queries, it will be missing between the DROP and CREATE -- this could have a serious adverse impact.

In my experience, it is usually unwise to have more than the PRIMARY KEY on a Data Warehouse "Fact" table. So, I wonder why you are even asking about other indexes.

Options: ReplyQuote


Subject
Views
Written By
Posted
1919
February 12, 2016 01:26AM
Re: Disable single index instead of all indexes
720
March 15, 2016 08:16PM


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.