MySQL Forums
Forum List  »  Partitioning

partitioning by primary key
Posted by: Yolie Bizana
Date: July 30, 2009 03:36AM

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_type_name` longtext,
`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

Options: ReplyQuote


Subject
Views
Written By
Posted
partitioning by primary key
3093
July 30, 2009 03:36AM
1875
August 08, 2009 04:39PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.