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.