CODEWITHSUNDEEP
mysqlpartitioningindexing
imperium2335
imperium2335

Reputation: 24122

MySQL Partitioning Unique Key

I am following this article:

http://www.chrismoos.com/2010/01/31/mysql-partitioning-tables-with-millions-of-rows

But when I run my query to partition my product table (that contains 500,000 rows) I get the error:

#1503 - A UNIQUE INDEX must include all columns in the table's partitioning function

My query is:

ALTER TABLE parts_library PARTITION by HASH(manufacturerId) PARTITIONS 200

My primary key is a compound key of id and manufacturerId, which is the same as in the article, so I don't understand why I am getting this error.

Here is the create statement for my table:

CREATE TABLE IF NOT EXISTS `parts_library` (
  `id` int(11) NOT NULL,
  `dateAdded` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `typeId` int(3) NOT NULL COMMENT 'Reference to part_types',
  `manufacturerId` int(11) NOT NULL DEFAULT '0',
  `familyId` int(11) NOT NULL DEFAULT '454',
  `partNumber` varchar(255) COLLATE utf8_unicode_ci NOT NULL COMMENT 'e.g. 6ES5123B62',
  `idealForm` varchar(255) COLLATE utf8_unicode_ci NOT NULL COMMENT 'E.g. 6ES5-123-B6/2',
  `comCodeId` int(11) DEFAULT NULL,
  `countryOriginId` int(3) NOT NULL DEFAULT '258',
  `minStockLevel` int(11) DEFAULT NULL,
  `weight` decimal(11,2) DEFAULT NULL,
  `width` decimal(11,2) DEFAULT NULL,
  `height` decimal(11,2) DEFAULT NULL,
  `depth` decimal(11,2) DEFAULT NULL,
  `validated` tinyint(1) NOT NULL DEFAULT '0',
  `onWeb` tinyint(1) DEFAULT '0',
  `indexed` tinyint(1) NOT NULL DEFAULT '1',
  `averageMargin` decimal(11,2) NOT NULL,
  PRIMARY KEY (`id`,`manufacturerId`),
  UNIQUE KEY `partNumber` (`partNumber`),
  KEY `fk_parts_library_parts_categories1` (`typeId`),
  KEY `fk_parts_library_manufacturers1` (`manufacturerId`),
  KEY `fk_parts_library_geo_countries1` (`countryOriginId`),
  KEY `fk_parts_library_parts_families1` (`familyId`),
  KEY `indexed` (`indexed`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

How can I partition my table?

Upvotes: 0

Views: 1664

Answers (1)

Stoleg
Stoleg

Reputation: 9300

The difference bewteen your code and the arcticle - you have a

UNIQUE KEY 'partNumber' ('partNumber')

which is not mentioned in the article. DB complains that you need to include column used for partitioning into key definition like:

UNIQUE KEY 'partNumber' ('manufacturerId','partNumber')

Upvotes: 1

Related Questions