MySQL Forums
Forum List  »  Partitioning

Create index on partition table (manually and dynamically)
Posted by: Vladimir Pivovar
Date: July 05, 2013 11:26PM

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.

Options: ReplyQuote


Subject
Views
Written By
Posted
Create index on partition table (manually and dynamically)
4834
July 05, 2013 11:26PM


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.