0

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?

1 Answer 1

1

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')

Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.