You could archive some records.

But old records are used somtimes

can we partition the table
or database partitioning possible for MySQL 5.0.47

http://www.mysqlperformanceblog.com/2008/12/22/high-performa nce-click-analysis-with-mysql/ ("sharding and partitioning" + comments)

You could split table into several different tables. One of criteria could be year (table1: from 0 to 2000, table2: from 2001 to 2003, etc). Then You rewrite Your queries to use UNION when it is needed.

here is table synatax

Create Table: CREATE TABLE `transaction` (
`requestId` bigint(20) NOT NULL,
`terminalId` varchar(255) default NULL,
`merchantId` varchar(255) default NULL,
`date` datetime default NULL,
`stan` varchar(255) default NULL,
`rrn` varchar(255) default NULL,
`amount` varchar(255) default NULL,
`pan` varchar(50) default NULL,
`type` varchar(50) default NULL,
`authcode` varchar(6) default NULL,
`encryptedTrack2Data` varchar(255) default NULL,
`lasttxnid` varchar(255) default NULL,
`status` char(1) default NULL,
`txnid` varchar(10) default NULL,
`lastcardtxnid` varchar(10) default NULL,
`fingerPrintFlag` varchar(3) default NULL,
`bankResponseCode` varchar(3) default NULL,
`modeOfOperation` varchar(20) default NULL,
`beneficiaryAccountNumber` varchar(30) default NULL,
`mobileTxnDate` datetime default NULL,
`zeroId` varchar(30) default NULL,
`zsn` varchar(30) default NULL,
PRIMARY KEY (`requestId`),
KEY `FK7FA0D2DECA2DC9DA` (`terminalId`),
KEY `pan` (`pan`),
KEY `merchantId` (`merchantId`),
KEY `INDEX_DATE` (`date`),
KEY `INDEX_PAN_CARD_SEQ` (`pan`,`lastcardtxnid`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

I want to make 3 partition on the bases of date column

as for year 0 t0 2000 other 2000 to 2008 other 2008 to 2009
what will be the syntax for this table as partitioning

hmm ... maybe:

CREATE TABLE `transaction0_1999` ...
CREATE TABLE `transaction2000_2007` ...
CREATE TABLE `transaction2008_2015` ...

The same structure, indexes etc. After You create tables, then (pseudocode):
1a. select * from transaction order by requestId limit 1;
1b. $row = mysql_fetch_assoc()
2a. parse $year from date column
2b. get $requestId
3. if ($year < 2000) $table = transaction0_1999
elseif( $year < 2008 ) $table = transaction2000_2007
else $table = transaction2008_2015
4. insert into + $table + $row
5. delete from transaction where requestId = $requestId
You can write it as partition.php and add it to the cron table:
* * * * * /usr/bin/php /path/to/the/partition.php
Cron will run that script every minute and it will move one row into new table. You could set bigger limit (100, 1000) and loop (1b ... 5) , so it will take less time (or for better performance You could remember requestId's and delete them in one query).


When everything is ready You could select from all the tables:
select * from transaction 0_1999 where condition
union
select * from transaction 2000_2007 where condition
union
select * from transaction 2008_2015 where condition
(I hope I wrote that query properly)