MySQL Forums
Forum List  »  Partitioning

Re: Create index on partition table (manually and dynamically)
Posted by: Vladimir Pivovar
Date: July 10, 2013 02:23PM

I came to the conclusion that most have to manually create a table for each month to fill in and build an index, then I will be able to index that table you really want to index, and not all all all Partition. Alas, I found a benefit: 12 million records added to indexed 3 billion rows in Partitions:

ALTER TABLE table_name DISABLE KEYS;
LOAD DATA ... INFILE ... ;
ALTER TABLE table_name ENABLE KEYS;

And now as a half-day is re-indexing, I understand that it will take a bit more than the original construction of the indices (about 24 hours)

And I just wanted to re-index a Partition, no more ...

I found one option, but whether it will help me?

WARNING:
Again, this is an undocumented, unsupported technique. Use it at your own risk, and back up your data first.

Here are the steps you'll need to take:

1) Create a table of the desired structure, but without any indexes.
2) Load the data into the table to build the. MYD file.
3) Create another empty table with the desired structure, this time including the indexes. This will create the. Frm and. MYI files you need.
4) Flush the tables with a read lock.
5) Rename the second table's. Frm and. MYI files, so MySQL uses them for the first table.
6) Release the read lock.
7) Use REPAIR TABLE to build the table's indexes. This will build all indexes by sorting, including the unique indexes.
This procedure can be much faster for very large tables.


http://my.safaribooksonline.com/book/-/ ... / id3544352



Edited 1 time(s). Last edit at 07/10/2013 02:28PM by Vladimir Pivovar.

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: Create index on partition table (manually and dynamically)
2843
July 10, 2013 02:23PM


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.