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?
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.
... / id3544352
Edited 1 time(s). Last edit at 07/10/2013 02:28PM by Vladimir Pivovar.