help for large tables

MySQL & MariaDB Other MySQL® Questions
1

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 ?

2

You could archive some records.

3

But old records are used somtimes

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

4

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.

5

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

6

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)