partitioning by primary key
This is my table structure:
CREATE TABLE `AM_transaction` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`created` datetime NOT NULL,
`effective_from` datetime NOT NULL,
`account_id` int(11) NOT NULL,
`related_account_id` int(11) DEFAULT NULL,
`type` int(11) NOT NULL,
`amount` decimal(12,2) NOT NULL,
`reference` varchar(50) DEFAULT NULL,
`linked_transaction_id` int(11) DEFAULT NULL,
`originating_transaction_id` int(11) DEFAULT NULL,
`accounting_period` datetime DEFAULT NULL,
`batch` int(11) DEFAULT NULL,
`detail_object_id` int(11) DEFAULT NULL,
`root_account_id` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `AM_transaction_related_account_id` (`related_account_id`),
KEY `AM_transaction_linked_transaction_id` (`linked_transaction_id`),
KEY `AM_transaction_originating_transaction_id` (`originating_transaction_id`),
KEY `AM_transaction_account_id_type_id_desc` (`account_id`,`type`,`id`),
KEY `AM_transaction_detail_object_id` (`detail_object_id`),
KEY `AM_transaction_account_id_created_id` (`account_id`,`created`,`id`),
KEY `AM_transaction_created` (`created`)
) ENGINE=InnoDB AUTO_INCREMENT=390293541 DEFAULT CHARSET=utf8
This table is currently 90GB, There is about 16 rows inserted per transaction and there is about 200 transactions per minute so it is growing quite quickly. this table has data from 2007 which is hardly needed so we would like to archive it by using partitioning.
Running queries from this table is a nightmare and i'd like to improve the response times.
I initialy wanted to use range partitioning on year(created)
but the problem is my created field is not a primary key and i cannot alter this.
So I need to partition by primary key which is id
Is there any function I can use to work out a partitioning function and in the end partitioning the data by oldest versus latest?
The queries are normally run only for the latest 3 months.
Any help would be much appreciated