1

When adding partitioning index on the column country_id to the following table, i get the error:

A PRIMARY KEY must include all columns in the table's partitioning function

I tried to add the column country_id to the PK containing then id and country_id. But then the similar error message occurs:

A UNIQUE KEY must include all columns in the table's partitioning function

CREATE TABLE `geo_city` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `country_id` smallint(5) unsigned NOT NULL,
  `admin_zone_id` bigint(20) unsigned DEFAULT NULL,
  `name` varchar(128) NOT NULL,
  `lat` double NOT NULL,
  `lng` double NOT NULL,
  `population` int(10) unsigned DEFAULT NULL,
  `timezone_id` smallint(5) unsigned DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `unique_city_id_per_country` (`id`,`country_id`),
  UNIQUE KEY `idx_unique_city_per_adminzone` (`admin_zone_id`,`name`),
  KEY `country_id` (`country_id`),
  KEY `name` (`name`),
  KEY `idx_lat_lng` (`lat`,`lng`),
  KEY `admin_zone_id` (`admin_zone_id`),
  KEY `population` (`population`),
  KEY `timezone_id` (`timezone_id`)
) ENGINE=InnoDB AUTO_INCREMENT=496831 DEFAULT CHARSET=utf8;

And then:

ALTER TABLE geo.geo_city PARTITION BY RANGE (country_id) (
                PARTITION p0 VALUES LESS THAN (2),
                PARTITION p1 VALUES LESS THAN (10),
                PARTITION p2 VALUES LESS THAN (20),
                PARTITION p3 VALUES LESS THAN (30),
                PARTITION pRemain VALUES LESS THAN MAXVALUE
            )

What to do now?

delete
  • 18,144
  • 15
  • 48
  • 79

2 Answers2

1

Try this definition:

CREATE TABLE `geo_city` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `country_id` smallint(5) unsigned NOT NULL,
  `admin_zone_id` bigint(20) unsigned DEFAULT NULL,
  `name` varchar(128) NOT NULL,
  `lat` double NOT NULL,
  `lng` double NOT NULL,
  `population` int(10) unsigned DEFAULT NULL,
  `timezone_id` smallint(5) unsigned DEFAULT NULL,
  PRIMARY KEY (`id`, country_id),
  UNIQUE KEY `idx_unique_city_per_adminzone` (`admin_zone_id`,`name`, country_id),
  KEY `name` (`name`),
  KEY `idx_lat_lng` (`lat`,`lng`),
  KEY `admin_zone_id` (`admin_zone_id`),
  KEY `population` (`population`),
  KEY `timezone_id` (`timezone_id`)
) ENGINE=InnoDB AUTO_INCREMENT=496831 DEFAULT CHARSET=utf8;

As the error suggests, each unique key needs to include country_id. Note: I'm not confident that country_id is really a good choice for partitioning, given that countries are of such widely different sizes. However, the question is not about the merits of your particular partitioning scheme.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • 1
    Since you are partitioning on `country_id`, putting that column at the beginning of those 2 keys is the least advantageous. Recommend moving it to the _end_ of the list. Also, `AUTO_INCREMENT` requires that `id` be at the beginning of _some_ index (It does not have to be `PRIMARY` or even `UNIQUE`). – Rick James Feb 20 '16 at 17:57
  • 1
    @RickJames . . . Is there somewhere that explains whether the partition key is better at the beginning or the end? – Gordon Linoff Feb 20 '16 at 19:49
  • 1
    It's a [Rule of Thumb](http://mysql.rjweb.org/doc.php/ricksrots#partitioning) I developed. If the `WHERE` clause has two criteria, use one for picking the partition, then the other(s) for getting clustering within the PK or secondary index. It is critical to my [efficient algorithm for finding the nearest pizza parlors](http://mysql.rjweb.org/doc.php/latlng). – Rick James Feb 20 '16 at 21:16
  • 1
    Also, with the partition key first, partitioning is no better than a non-partitioned table with the same index(es). – Rick James Feb 20 '16 at 21:18
1

You need to add all partition key(s) into all primary key and unique keys.

Dylan Su
  • 5,975
  • 1
  • 16
  • 25