Robert Saylor
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.
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.
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.
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.
Regards,
Tomas Helgi
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)
);
Regards,
Tomas Helgi
ASKER
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
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Ah thanks! Will mess with this more Monday morning but I think I see what you are talking about now.
ASKER
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
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
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
Any more problems regarding the partitioning ?
Regards,
Tomas Helgi
ASKER
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
Did you manage to solve this ?
Regards,
Tomas Helgi
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