MySQL Forums
Forum List  »  Partitioning

Re: Index management in a partitioned table
Posted by: Rick James
Date: October 02, 2016 12:40PM

Yes to all questions.

Think about PARTITIONing this way:

Each partition is a separate table, complete with an index. The partitions operate virtually independently.

TRUNCATE throws away all data and index entries for the partition being truncated.

INSERT/SELECT/DELETE/etc into the table first try to do "partition pruning", which is picking which partition (or partitions) to operate on. Then the operation is handed to each relevant partition to perform. At this point it is identical to the operation acting on a table.

I do not understand why you have a 2-partition table. Here is a common thing I advise; it sounds similar:

When rebuilding a table, do it this way:

1. CREATE TABLE new LIKE real;
2. load `new` by whatever means (LOAD DATA, INSERT, etc)
3. RENAME TABLE real TO old, new TO real;
4. DROP TABLE old;

Step 2 is the slow process.
Step 3 is instantaneous; the rest of the code continues to operate on `real`, oblivious to the swap that is occurring.

Options: ReplyQuote


Subject
Views
Written By
Posted
692
September 29, 2016 12:14AM
Re: Index management in a partitioned table
448
October 02, 2016 12:40PM


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.