Are there any disadvantages to partitioning on financial year?

Question

Our current set up has one table per financial year (May 1- April 30). Each table has approx 1.5 million rows. We have about 8 years of data, and will obviously be adding each year.

The majority of queries are within the financial year/one partition. Either select * from sales where date time between '2013-05-01' and '2014-04-30 23:59:59' or some period within that year.

My plan is to have a range partition on an InnoDB table. e.g.

PARTITION BY RANGE COLUMNS(datetime)
(PARTITION p0 VALUES LESS THAN ('1999-05-01') ENGINE = InnoDB,
 PARTITION p1 VALUES LESS THAN ('2000-05-01') ENGINE = InnoDB,
 PARTITION p2 VALUES LESS THAN ('2001-05-01') ENGINE = InnoDB,
...

This means that the PK has to become PRIMARY KEY (index,datetime).

Are there any significant disadvantages to partitioning compared to having an unpartitioned table? I know that means the PK is now length 12 and all further indexes will have that prepended to it. Does that make a difference? The table needs to work faster on reads than writes, and there are a fair few indexes on it.

• Is there any functional difference between partitioning on RANGE COLUMNS(datetime) and RANGE TO_DAYS(datetime) using MySQL 5.5+?
• Is partitioning on datetime OK? One alternative is adding in a column for financial year and indexing/partitioning on that? (And then adding another index on datetime for other queries).

We do sometimes need to query the time across all time or over "the last X months", but this is pretty rare. The main advantages of moving to a single table is to eliminate the logic in the application working out which table to insert/update/select and not needing to calculate unions in those situations where we need more than one table.

Answer 1

Answering your questions above:

1. In RANGE COLUMNS (datetime) key word COLUMNS is unnecessary. It is used to specify a list of columns, where as you have only 1 column. The difference exists: for RANGE (datetime_column) you can specify partition boundaries in datetime format, whereas for RANGE TO_DAYS(datetime_column) partition boundaries should be effectively in date format.

2. Partitioning on datetime is OK and it is very often.

Please refer to MySQL Manual, Chapter 18. Partitioning.

If the only reason for you using partitioning is structuring data, then may I suggest you looking at MERGE Storage Engine. It allows to create a table consisting of other tables. When queries to this overarching table have data only in some tables, then only those tables are used. It is easier to set up and maintain, than partitioning.