Skip navigation links

MySQL Forums :: Partitioning :: Help to optimize subpartition


Advanced Search

Help to optimize subpartition
Posted by: Paki Paki ()
Date: April 19, 2012 05:28AM

I have an actively used big table with 500M records in it:

CREATE TABLE IF NOT EXISTS `bigtable` (
`userid` int(11) unsigned NOT NULL,
`status` tinyint(1) unsigned NOT NULL DEFAULT '0',
`dt` int(11) DEFAULT NULL, -- represents timestamp
KEY `dt` (`dt`),
KEY `uf` (`userid`,`fdt`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC
PARTITION BY RANGE (dt)
SUBPARTITION BY HASH (userid)
SUBPARTITIONS 96
(PARTITION p2000 VALUES LESS THAN (946684800) ENGINE = MyISAM,
PARTITION p2011q2 VALUES LESS THAN (1309478400) ENGINE = MyISAM,
PARTITION p2011q3 VALUES LESS THAN (1317427200) ENGINE = MyISAM,
PARTITION p2011q4 VALUES LESS THAN (1325376000) ENGINE = MyISAM,
PARTITION p2012q1 VALUES LESS THAN (1333238400) ENGINE = MyISAM,
PARTITION p2012q2 VALUES LESS THAN (1341100800) ENGINE = MyISAM,
PARTITION p2012q3 VALUES LESS THAN (1349049600) ENGINE = MyISAM,
PARTITION p1 VALUES LESS THAN MAXVALUE ENGINE = MyISAM)

I need to optimize table after periodical bulk data deletions.
I can execute 'ALTER TABLE `bigtable` OPTIMIZE PARTITION `p2000`;' to optimize one partition. But it can take a long time and cause some blocking issues.

So I need to optimize table by subpartitions. Please help with ALTER command for optimize any subpartition.
Command 'ALTER TABLE `bigtable` OPTIMIZE PARTITION `p2000sp1`;' doesn't work.

Options: ReplyQuote


Subject Views Written By Posted
Help to optimize subpartition 1556 Paki Paki 04/19/2012 05:28AM
Re: Help to optimize subpartition 966 Rick James 04/20/2012 06:54PM
Re: Help to optimize subpartition 933 Paki Paki 04/21/2012 04:15PM
Re: Help to optimize subpartition 1026 Mattias Jonsson 05/02/2012 07:59AM
Re: Help to optimize subpartition 791 Mattias Jonsson 05/02/2012 01:41PM


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.