0

I'm trying create a partition in mysql on a date column MV_DATE is of type DATE

Here's the query -

ALTER TABLE table_name PARTITION BY RANGE (TO_DAYS(MV_DATE))(PARTITION p0 VALUES LESS THAN (TO_DAYS('2015-08-31')));

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

MV_DATE isn't the primary key of my table.

I also tried ALTER TABLE JOB_VOL_SWH PARTITION BY RANGE ((MV_DATE))(PARTITION p0 VALUES LESS THAN (TO_DAYS('2015-08-31'))); which throws the error ERROR 1659 (HY000): Field 'JB_DATE' is of a not allowed type for this type of partitioning

I've a tried a few options now but none seem to work.

Is there a special way to create partitioning by date ranges? The MYSQL table has close to a billion rows of data which pretty much means my queries don't return at all (virtually). Hence, looking to partition the tables.

usert4jju7
  • 1,653
  • 3
  • 27
  • 59

1 Answers1

2

19.6.1 Partitioning Keys, Primary Keys, and Unique Keys

This section discusses the relationship of partitioning keys with primary keys and unique keys. The rule governing this relationship can be expressed as follows: All columns used in the partitioning expression for a partitioned table must be part of every unique key that the table may have.

In other words, every unique key on the table must use every column in the table's partitioning expression. (This also includes the table's primary key, since it is by definition a unique key. This particular case is discussed later in this section.) For example, each of the following table creation statements is invalid:

Reference:

https://dev.mysql.com/doc/refman/5.7/en/partitioning-limitations-partitioning-keys-unique-keys.html

Dylan Su
  • 5,975
  • 1
  • 16
  • 25
  • Thank you Dylan. The column `MV_DATE` can't be unique in my case. None of the columns in the table can be unique except the dummy primary key which is an auto increment integer. There isn't meaning in partitioning by this I guess as I don't use it any of my queries. Is there no other way to partition the table then? – usert4jju7 Mar 21 '16 at 05:19
  • 3
    Wrong. An `AUTO_INCREMENT` does _not_ have to be by itself. `PRIMARY KEY(id, mv_date)` is fine. All AI needs is to find `MAX(id)` when you first open the table; and that PK is sufficient for it. You do, however, lose the check for `INSERTing` a duplicate `id`. But no one ever does that. [More on partitioning, especially `BY RANGE(TO_DAYS(..))`](http://mysql.rjweb.org/doc.php/partitionmaint). – Rick James Mar 21 '16 at 05:48
  • Rick is right. The non-unique mv_date can be add into primary key as a composite primary key. – Dylan Su Mar 21 '16 at 06:21