GET 24/7 LIVE HELP NOW

Announcement

Announcement Module
Collapse
No announcement yet.

help for large tables

Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • help for large tables

    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.

    Comment


    • #3
      But old records are used somtimes

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

      Comment


      • #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.

        Comment


        • #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

          Comment


          • #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)

            Comment

            Working...
            X