Create index on partition table (manually and dynamically)

[Vladimir Pivovar]

How does the presence of the index (s) at the time of the construction of other indexes?

I have the build of the first index took 9 hours (a component on the 4 fields)
and build time (!) the second index (composite of 3 x fields, which is the first in positions 2,3,4) took 15 hours.

The idea is a second faster than the first index should be built, and on the contrary it is 2.5 times as long to build.



CREATE TABLE `amarecord` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, <------------------
`answer` tinyint(1) NOT NULL DEFAULT '0',
`dateOfCall` date NOT NULL DEFAULT '0000-00-00',

`datetime_unix` int(11) unsigned NOT NULL DEFAULT '0',
`phoneNumberA` varchar(32) NOT NULL DEFAULT '-',
`phoneNumberB` varchar(32) NOT NULL DEFAULT '-',
.......

PRIMARY KEY (`id`,`dateOfCall`) <------------------

PARTITION BY RANGE ( YEAR(dateOfCall)-1)
SUBPARTITION BY HASH ( MONTH(dateOfCall))
SUBPARTITIONS 13
(
PARTITION p_2010 VALUES LESS THAN (2010) ENGINE = MyISAM,
PARTITION p_2011 VALUES LESS THAN (2011) ENGINE = MyISAM,
PARTITION p_2012 VALUES LESS THAN (2012) ENGINE = MyISAM,
PARTITION p_2013 VALUES LESS THAN (2013) ENGINE = MyISAM,
)

After I added the id, the status of "copying to tmp table" hung over 14 hours and I stopped the process:

CREATE INDEX PhoneA_PhoneB_Answer_Datum ON amarecord(PhoneNumberA,PhoneNumberB,answer,datetime_unix) USING BTREE;

CREATE INDEX PhoneB_Answer_Datum ON amarecord(PhoneNumberB,answer,datetime_unix) USING BTREE;

And this construction is performed in PRIMARY KEY (`id`, `dateOfCall`).

The question of why the presence of PRIMARY KEY (`id`, `dateOfCall`) had such an impact on the construction of the new indexes.
And how to build an index for Partitioning, for each fragment, or for the entire table as a whole?
How to build a separate index for a particular section separately, if at all possible?

P.S.

Launched indexing tables, process status "Repair by sorting" more than 3 hours.
Free space on hard drive has decreased the value of the entire table
What does it mean that the index is built directly across the table?

Is there such a command for build an index for each partition:
Create INDEX partition_2010 ON p_2010 (fielnames)
Create INDEX partition_2011 ON p_2011 (fielnames)
Create INDEX partition_2012 ON p_2010 (fielnames) ?



Edited 3 time(s). Last edit at 07/06/2013 11:32PM by Vladimir Pivovar.