Skip navigation links

MySQL Forums :: Partitioning :: Create index on partition table (manually and dynamically)


Advanced Search

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) 1898 Vladimir Pivovar 07/05/2013 11:26PM
Re: Create index on parttion table (manually and dynamically) 715 Rick James 07/06/2013 10:52PM
Re: Create index on parttion table (manually and dynamically) 649 Vladimir Pivovar 07/07/2013 12:20AM
Re: Create index on parttion table (manually and dynamically) 620 Vladimir Pivovar 07/07/2013 10:57AM
Re: Create index on parttion table (manually and dynamically) 668 Rick James 07/08/2013 09:46AM
Re: Create index on partition table (manually and dynamically) 696 Vladimir Pivovar 07/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.