# help for large tables

**URL:** <https://forums.percona.com/t/help-for-large-tables/1202>\
**Category:** Other MySQL® Questions\
**Created:** [July 4, 2009, 3:51am UTC](https://forums.percona.com/t/help-for-large-tables/1202 "2009-07-04T03:51:41Z")\
**Posts on this page:** 6\
**Page:** 1

<div class="post-metadata">

**Author:** ![ankur02018](https://avatars.discourse-cdn.com/v4/letter/a/838e76/32.png) [@ankur02018](https://forums.percona.com/u/ankur02018)\
**Post date:** [July 4, 2009, 3:51am UTC](https://forums.percona.com/t/help-for-large-tables/1202/1 "2009-07-04T03:51:41Z")

</div>

Hi ,  
I have database which has some tables that contain huge records  
upto 8 Million due to this even making indexes queries excution timing  
will be too high

Please provide solution for this what step we can take ?

---

<div class="post-metadata">

**Author:** ![januzi](https://avatars.discourse-cdn.com/v4/letter/j/82dd89/32.png) [@januzi](https://forums.percona.com/u/januzi)\
**Post date:** [July 4, 2009, 4:28am UTC](https://forums.percona.com/t/help-for-large-tables/1202/2 "2009-07-04T04:28:32Z")

</div>

You could archive some records.

---

<div class="post-metadata">

**Author:** ![ankur02018](https://avatars.discourse-cdn.com/v4/letter/a/838e76/32.png) [@ankur02018](https://forums.percona.com/u/ankur02018)\
**Post date:** [July 4, 2009, 5:00am UTC](https://forums.percona.com/t/help-for-large-tables/1202/3 "2009-07-04T05:00:27Z")

</div>

But old records are used somtimes

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

---

<div class="post-metadata">

**Author:** ![januzi](https://avatars.discourse-cdn.com/v4/letter/j/82dd89/32.png) [@januzi](https://forums.percona.com/u/januzi)\
**Post date:** [July 4, 2009, 5:09am UTC](https://forums.percona.com/t/help-for-large-tables/1202/4 "2009-07-04T05:09:08Z")

</div>

[http://www.mysqlperformanceblog.com/2008/12/22/high-performa](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.

---

<div class="post-metadata">

**Author:** ![ankur02018](https://avatars.discourse-cdn.com/v4/letter/a/838e76/32.png) [@ankur02018](https://forums.percona.com/u/ankur02018)\
**Post date:** [July 4, 2009, 7:32am UTC](https://forums.percona.com/t/help-for-large-tables/1202/5 "2009-07-04T07:32:30Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![januzi](https://avatars.discourse-cdn.com/v4/letter/j/82dd89/32.png) [@januzi](https://forums.percona.com/u/januzi)\
**Post date:** [July 4, 2009, 9:18am UTC](https://forums.percona.com/t/help-for-large-tables/1202/6 "2009-07-04T09:18:21Z")

</div>

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)