Link to home
Start Free TrialLog in
Avatar of Robert Saylor
Robert SaylorFlag for United States of America

asked on

MySQL Partitioning

I have several existing tables reaching up to several million rows and I want to start adding partitioning to them. I took a smaller table with a couple thousand records as a test bed but not really sure how to perform this on existing tables.

My end goal is to create a partition for each month_year

IE: I would call the partition 2017_01 , 2017_02; etc and want data with the date range to be in each of those partitions.

Using MySQL 5.7 on a google cloud sql env.
Avatar of Tomas Helgi Johannsson
Tomas Helgi Johannsson
Flag of Iceland image

Hi!

Take a look at my article on table partitioning.
One of my examples there partition the data by year (range partitioning which would be the best choice). I would then recommend you to have the months as subpartitions (see my article on subpartioning and here

Use ALTER table add partiton commands to existing to add partitioning to non-partition tables as well as add additional partitions.
See also other usefull ALTER TABLE commands and what they do in for partitioning in bottom of my article.

Regards,
    Tomas Helgi
Avatar of Robert Saylor

ASKER

Hi Tomas,

I actually was looking at that the other day. Can you provide a SQL query for updating an existing table to add a partition? That is where I am not sure.
Hi!

Something like this.

This will partion your table by year.

ALTER TABLE mynonparttable
PARTITION BY RANGE (YEAR(mydatecolumn)) (
  PARTITION p0 values less than (2000),
  PARTITION p1 values less than (2001),
  PARTITION p2 values less than (2002)
);

Open in new window


Regards,
    Tomas Helgi
Thanks but got an error.

Static analysis:

1 errors were found during analysis.

Unrecognized alter operation. (near "" at position 0)
SQL query:

ALTER TABLE tablename PARTITION BY RANGE (YEAR(system_date)) ( PARTITION p0 values less than (2000), PARTITION p1 values less than (2001), PARTITION p2 values less than (2002) )

MySQL said: Documentation

#1486 - Constant, random or timezone-dependent expressions in (sub)partitioning function are not allowed
ASKER CERTIFIED SOLUTION
Avatar of Tomas Helgi Johannsson
Tomas Helgi Johannsson
Flag of Iceland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Ah thanks! Will mess with this more Monday morning but I think I see what you are talking about now.
I changed the type to DATE for "system_date" but got errors. My column labeled as "id" is my primary key.

Error
Static analysis:

1 errors were found during analysis.

Unrecognized alter operation. (near "" at position 0)
SQL query:

ALTER TABLE tablename PARTITION BY RANGE(YEAR(system_date))( PARTITION p0 VALUES LESS THAN(2000), PARTITION p1 VALUES LESS THAN(2001), PARTITION p2 VALUES LESS THAN(2002) )

MySQL said: Documentation

#1503 - A PRIMARY KEY must include all columns in the table's partitioning function
Hi!

One thing that I don't mention in my article but are well known regarding partitioning in any database is that if a table has a primary key then the partitioning key must contain at least one of the columns used in the primary key.
That is the partitioning key must be present in a unique key ( be at least  a subset of a unique key).
This is mentioned in this article though


Regards,
     Tomas Helgi
Hi!

Any more problems regarding the partitioning ?

Regards,
     Tomas Helgi
sorry, still not working but I have not had time to go back and troubleshoot more. Will try this week.
Hi Robert!

Did you manage to solve this ?

Regards,
     Tomas Helgi