mysql partition by bigint and a primary key(id)

Question

im testing now for +10hours to get a database structure with a primary key (id) and a partition by bigint. but nothing will work :/ is that possible? maybe anybody could give a me good hint ;)

CREATE TABLE IF NOT EXISTS `test` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `uniqueID` bigint(20) DEFAULT NULL,
  `value` int(11) DEFAULT NULL,
  `m1` text CHARACTER SET utf8,
  `m2` text CHARACTER SET utf8,
  `m3` text CHARACTER SET utf8,
  `m4` text CHARACTER SET utf8,
  `m5` text CHARACTER SET utf8,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniqueID` (`uniqueID`),
  KEY `value` (`value`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci PACK_KEYS=1 DELAY_KEY_WRITE=1 ROW_FORMAT=DYNAMIC
/*!50500 PARTITION BY RANGE  COLUMNS(uniqueID)
(PARTITION p1 VALUES LESS THAN ('0') ENGINE = MyISAM,
 PARTITION p2 VALUES LESS THAN ('1') ENGINE = MyISAM,
 PARTITION p3 VALUES LESS THAN ('2') ENGINE = MyISAM,
 PARTITION p4 VALUES LESS THAN ('3') ENGINE = MyISAM,
 PARTITION p5 VALUES LESS THAN ('4') ENGINE = MyISAM,
 PARTITION p6 VALUES LESS THAN ('5') ENGINE = MyISAM,
 PARTITION p7 VALUES LESS THAN ('6') ENGINE = MyISAM,
 PARTITION p8 VALUES LESS THAN ('9') ENGINE = MyISAM,
 PARTITION p9 VALUES LESS THAN (MAXVALUE) ENGINE = MyISAM) */;

with this partition, i will split the bigint values by the first number - example:

16275214652090176103 would be a part of partition p2

this database will take 100M records :/

thanks advance

score 0 · Accepted Answer · answered Mar 20 '14 at 13:04

The column you are partitioning on must be part of the primary key of the table, and I believe, it must be the last column in the primary key. So, if you were to define your primary key as PRIMARY KEY (id, uniqueID), you should be able to partition on uniqueID.

That being said, given that your uniqueID field is a bigint and you are trying to partition based on the bigint being less than a string, I'm not sure what you are trying to do will work as desired. Perhaps using hash partitioning rather than range partitioning would be more use to you? For more details see https://dev.mysql.com/doc/refman/5.1/en/partitioning-hash.html

thanks, i got it working with primary key now ;-) but i cant get it works with partition uniqueID bigint - it works with binary, but i think it would be faster with bigint — kewl, Mar 20 '14 at 13:08

mysql partition by bigint and a primary key(id)

1 Answers1