Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
add comment

1 Answer

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.

share|improve this answer
    
Perhaps you can add that the Merge engine does not support foreign keys. –  ypercube Oct 25 '13 at 5:36
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.