Mysql partitioning on table with Md5 unique key

Question

I have problem making partition on large table with a few million rows.

CREATE TABLE  `searcheg`.`pages` (
  `urlId` int(9) NOT NULL AUTO_INCREMENT,
  `url` varchar(1024) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  `urlhash` binary(16) NOT NULL,<< MD5 unhex.
  PRIMARY KEY (`urlId`),
  UNIQUE KEY `urlhash` (`urlhash`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

How do I partition this? urlhash must be unique as I need to insert large number of rows and check for repeats. As partition must include all unique column and cannot be non integer value. One reason for partition is to reduce the .ibd for more manageable size as it grows over 50G and I want to split them. urlID as primary key is to prevent the row reorder each time insert is made. urlhash as unique to prevent duplicate entry. I am looking for some way to split the table without having to drop the primary key.

I wouldn't partition using md5 because it is a very strong but slow hash function. It is meant for a different purpose. — usr

RandomSeed RandomSeed · Accepted Answer · 2012-10-16T01:21:52

If you are using MySQL v5.5, then you can use the new PARTITION BY RANGE COLUMNS option. If you can drop the redundant urlID column and define usrhash as the primary key, then your table definition could look like that:

CREATE TABLE pages (
  url VARCHAR(1024) NOT NULL,
  urlhash BINARY(16) NOT NULL,
  PRIMARY KEY (urlhash)
)
PARTITION BY RANGE COLUMNS (urlhash) (
  PARTITION p0 VALUES LESS THAN ('j'),
  PARTITION p1 VALUES LESS THAN ('r'),  -- split in as many ranges as you wish
  PARTITION p2 VALUES LESS THAN (MAXVALUE)
) ;

In case the urlID is required (e.g. by a framework), then I don't know, because a primary key or a unique index "must include all columns in the table's partitioning function"...

Mysql partitioning on table with Md5 unique key

1 Answers