MySQL Forums :: Partitioning :: Index management in a partitioned table


Advanced Search

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
Index management in a partitioned table 436 Rathi Rao 09/29/2016 12:14AM
Re: Index management in a partitioned table 300 Rick James 10/02/2016 12:40PM
Re: Index management in a partitioned table 233 Rathi Rao 10/03/2016 11:59PM


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.