Hi Rick James,
Thanks for your reply.
> for the artran table structure, please refer below (huge table):
CREATE TABLE `artran` (
`TYPE` varchar(4) NOT NULL DEFAULT '',
`REFNO` varchar(50) NOT NULL DEFAULT '',
`REFNO2` varchar(24) NOT NULL DEFAULT '',
`OLD_REFNO` varchar(24) NOT NULL DEFAULT '',
`REVISION` int(4) unsigned NOT NULL DEFAULT '0',
`TRANCODE` int(4) NOT NULL DEFAULT '0',
`CUSTNO` varchar(12) NOT NULL DEFAULT '',
`FPERIOD` varchar(2) NOT NULL DEFAULT '',
`WOS_DATE` date NOT NULL DEFAULT '0000-00-00',
`TRAN_DATE` date NOT NULL DEFAULT '0000-00-00',
`DESP` varchar(40) NOT NULL DEFAULT '',
`DESPA` varchar(40) NOT NULL DEFAULT '',
`AGENNO` varchar(20) NOT NULL DEFAULT '',
`AREA` varchar(12) NOT NULL DEFAULT '',
`SOURCE` varchar(40) DEFAULT '',
`JOB` varchar(40) DEFAULT '',
`CURRRATE` double(16,10) NOT NULL DEFAULT '0.0000000000',
`GROSS_BIL` double(15,5) NOT NULL DEFAULT '0.00000',
`DISC1_BIL` double(15,5) NOT NULL DEFAULT '0.00000',
`DISC2_BIL` double(15,5) NOT NULL DEFAULT '0.00000',
`DISC3_BIL` double(15,5) NOT NULL DEFAULT '0.00000',
`DISC_BIL` double(15,5) NOT NULL DEFAULT '0.00000',
`NET_BIL` double(15,5) NOT NULL DEFAULT '0.00000',
`TAX1_BIL` double(15,5) NOT NULL DEFAULT '0.00000',
`TAX2_BIL` double(15,5) NOT NULL DEFAULT '0.00000',
`TAX3_BIL` double(15,5) NOT NULL DEFAULT '0.00000',
`TAX_BIL` double(15,5) NOT NULL DEFAULT '0.00000',
`GRAND_BIL` double(15,5) NOT NULL DEFAULT '0.00000',
`DEBIT_BIL` double(15,5) NOT NULL DEFAULT '0.00000',
`CREDIT_BIL` double(15,5) NOT NULL DEFAULT '0.00000',
`INVGROSS` double(15,5) NOT NULL DEFAULT '0.00000',
`DISP1` double(10,5) NOT NULL DEFAULT '0.00000',
`DISP2` double(10,5) NOT NULL DEFAULT '0.00000',
`DISP3` double(10,5) NOT NULL DEFAULT '0.00000',
`DISCOUNT1` double(15,5) NOT NULL DEFAULT '0.00000',
`DISCOUNT2` double(15,5) NOT NULL DEFAULT '0.00000',
`DISCOUNT3` double(15,5) NOT NULL DEFAULT '0.00000',
`DISCOUNT` double(15,5) NOT NULL DEFAULT '0.00000',
`NET` double(15,5) NOT NULL DEFAULT '0.00000',
`TAX1` double(15,5) NOT NULL DEFAULT '0.00000',
`TAX2` double(15,5) NOT NULL DEFAULT '0.00000',
`TAX3` double(15,5) NOT NULL DEFAULT '0.00000',
`TAX` double(15,5) NOT NULL DEFAULT '0.00000',
`TAXP1` double(5,2) NOT NULL DEFAULT '0.00',
`TAXP2` double(5,2) NOT NULL DEFAULT '0.00',
`TAXP3` double(5,2) NOT NULL DEFAULT '0.00',
`GRAND` double(15,5) NOT NULL DEFAULT '0.00000',
`DEBITAMT` double(15,5) NOT NULL DEFAULT '0.00000',
`CREDITAMT` double(15,5) NOT NULL DEFAULT '0.00000',
`MC1_BIL` double(15,5) NOT NULL DEFAULT '0.00000',
`MC2_BIL` double(15,5) NOT NULL DEFAULT '0.00000',
`M_CHARGE1` double(15,5) NOT NULL DEFAULT '0.00000',
`M_CHARGE2` double(15,5) NOT NULL DEFAULT '0.00000',
`CS_PM_CASH` double(15,5) NOT NULL DEFAULT '0.00000',
`CS_PM_CHEQ` double(15,5) NOT NULL DEFAULT '0.00000',
`CS_PM_CRCD` double(15,5) NOT NULL DEFAULT '0.00000',
`CS_PM_CRC2` double(15,5) NOT NULL DEFAULT '0.00000',
`CS_PM_TT` double(15,5) NOT NULL DEFAULT '0.00000',
`CS_PM_DBCD` double(15,5) NOT NULL DEFAULT '0.00000',
`CS_PM_VOUC` double(15,5) NOT NULL DEFAULT '0.00000',
`DEPOSIT` double(15,5) NOT NULL DEFAULT '0.00000',
`CS_PM_DEBT` double(15,5) NOT NULL DEFAULT '0.00000',
`CS_PM_WHT` double(15,5) NOT NULL DEFAULT '0.00000',
`CHECKNO` varchar(12) NOT NULL DEFAULT '',
`IMPSTAGE` varchar(12) NOT NULL DEFAULT '',
`BILLCOST` double(15,5) NOT NULL DEFAULT '0.00000',
`BILLSALE` double(15,5) NOT NULL DEFAULT '0.00000',
`PAIDDATE` date NOT NULL DEFAULT '0000-00-00',
`PAIDAMT` double(17,5) NOT NULL DEFAULT '0.00000',
`REFNO3` varchar(24) NOT NULL DEFAULT '',
`AGE` varchar(2) NOT NULL DEFAULT '',
`NOTE` varchar(8) NOT NULL DEFAULT '',
`TERM` varchar(12) NOT NULL DEFAULT '',
`ISCASH` varchar(2) NOT NULL DEFAULT '',
`VAN` varchar(80) NOT NULL DEFAULT '',
`DEL_BY` varchar(12) NOT NULL DEFAULT '',
`PLA_DODATE` date NOT NULL DEFAULT '0000-00-00',
`ACT_DODATE` date NOT NULL DEFAULT '0000-00-00',
`URGENCY` char(1) NOT NULL DEFAULT '',
`CURRRATE2` double(16,5) NOT NULL DEFAULT '0.00000',
`STAXACC` varchar(8) NOT NULL DEFAULT '',
`SUPP1` varchar(8) NOT NULL DEFAULT '',
`SUPP2` varchar(8) NOT NULL DEFAULT '',
`PONO` text,
`DONO` text,
`REM0` varchar(35) NOT NULL DEFAULT '',
`REM1` varchar(35) NOT NULL DEFAULT '',
`REM2` varchar(35) NOT NULL DEFAULT '',
`REM3` varchar(35) NOT NULL DEFAULT '',
`REM4` varchar(35) NOT NULL DEFAULT '',
`REM5` varchar(80) NOT NULL DEFAULT '',
`REM6` varchar(80) NOT NULL DEFAULT '',
`REM7` varchar(80) NOT NULL DEFAULT '',
`REM8` varchar(80) NOT NULL DEFAULT '',
`REM9` varchar(80) NOT NULL DEFAULT '',
`REM10` varchar(35) NOT NULL DEFAULT '',
`REM11` varchar(35) NOT NULL DEFAULT '',
`REM12` varchar(35) NOT NULL DEFAULT '',
`permitno` varchar(200) NOT NULL DEFAULT '',
`FREM0` varchar(80) NOT NULL DEFAULT '',
`FREM1` varchar(80) NOT NULL DEFAULT '',
`FREM2` varchar(80) NOT NULL DEFAULT '',
`FREM3` varchar(80) NOT NULL DEFAULT '',
`FREM4` varchar(80) NOT NULL DEFAULT '',
`FREM5` varchar(80) NOT NULL DEFAULT '',
`FREM6` varchar(80) NOT NULL DEFAULT '',
`FREM7` varchar(80) NOT NULL DEFAULT '',
`FREM8` varchar(80) NOT NULL DEFAULT '',
`FREM9` varchar(80) NOT NULL DEFAULT '',
`COMM1` varchar(60) NOT NULL DEFAULT '',
`COMM2` varchar(60) NOT NULL DEFAULT '',
`COMM3` varchar(60) NOT NULL DEFAULT '',
`COMM4` varchar(60) NOT NULL DEFAULT '',
`ID` char(1) NOT NULL DEFAULT '',
`GENERATED` char(1) NOT NULL DEFAULT '',
`TOINV` varchar(100) DEFAULT '',
`ORDER_CL` char(1) NOT NULL DEFAULT '',
`EXPORTED` varchar(24) NOT NULL DEFAULT '',
`EXPORTED1` date NOT NULL DEFAULT '0000-00-00',
`EXPORTED2` varchar(24) NOT NULL DEFAULT '',
`EXPORTED3` date NOT NULL DEFAULT '0000-00-00',
`LAST_YEAR` char(1) NOT NULL DEFAULT '',
`POSTED` char(1) NOT NULL DEFAULT '',
`postedubs` varchar(10) DEFAULT '',
`PRINTED` char(1) NOT NULL DEFAULT '',
`LOKSTATUS` char(1) NOT NULL DEFAULT '',
`VOID` char(1) NOT NULL DEFAULT '',
`NAME` varchar(40) NOT NULL DEFAULT '',
`PHONEA` varchar(35) NOT NULL DEFAULT '',
`PONO2` varchar(35) NOT NULL DEFAULT '',
`DONO2` varchar(35) NOT NULL DEFAULT '',
`CSGTRANS` varchar(3) NOT NULL DEFAULT '',
`TAXINCL` char(1) NOT NULL DEFAULT '',
`TABLENO` varchar(4) NOT NULL DEFAULT '',
`CASHIER` varchar(8) NOT NULL DEFAULT '',
`MEMBER` varchar(20) NOT NULL DEFAULT '',
`COUNTER` varchar(8) NOT NULL DEFAULT '',
`TOURGROUP` varchar(3) NOT NULL DEFAULT '',
`TRDATETIME` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
`TIME` varchar(8) NOT NULL DEFAULT '',
`XTRCOST` double(15,5) NOT NULL DEFAULT '0.00000',
`XTRCOST2` double(15,5) NOT NULL DEFAULT '0.00000',
`POINT` double(12,5) NOT NULL DEFAULT '0.00000',
`USERID` varchar(50) NOT NULL DEFAULT '',
`BPERIOD` varchar(2) NOT NULL DEFAULT '',
`VPERIOD` varchar(2) NOT NULL DEFAULT '',
`BDATE` date NOT NULL DEFAULT '0000-00-00',
`CURRCODE` varchar(15) NOT NULL DEFAULT '',
`COMM0` varchar(60) DEFAULT '',
`REM13` varchar(35) NOT NULL DEFAULT '',
`REM14` varchar(35) NOT NULL DEFAULT '',
`MC3_BIL` double(15,5) NOT NULL DEFAULT '0.00000',
`MC4_BIL` double(15,5) NOT NULL DEFAULT '0.00000',
`MC5_BIL` double(15,5) NOT NULL DEFAULT '0.00000',
`MC6_BIL` double(15,5) NOT NULL DEFAULT '0.00000',
`MC7_BIL` double(15,5) NOT NULL DEFAULT '0.00000',
`M_CHARGE3` double(15,5) NOT NULL DEFAULT '0.00000',
`M_CHARGE4` double(15,5) NOT NULL DEFAULT '0.00000',
`M_CHARGE5` double(15,5) NOT NULL DEFAULT '0.00000',
`M_CHARGE6` double(15,5) NOT NULL DEFAULT '0.00000',
`M_CHARGE7` double(15,5) NOT NULL DEFAULT '0.00000',
`SPECIAL_ACCOUNT_CODE` varchar(8) NOT NULL DEFAULT '',
`CREATED_BY` varchar(50) NOT NULL DEFAULT '',
`UPDATED_BY` varchar(50) NOT NULL DEFAULT '',
`CREATED_ON` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
`UPDATED_ON` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
`consignment` varchar(45) DEFAULT '',
`PACKED` varchar(45) DEFAULT 'N',
`PACKED_ON` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
`PACKED_BY` varchar(100) DEFAULT NULL,
`eInvoice_Submited` varchar(45) DEFAULT NULL,
`SUBMITED_ON` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
`PONODATE` date DEFAULT NULL,
`IRAS_POSTED` char(1) NOT NULL DEFAULT '',
`voucher` varchar(45) DEFAULT NULL,
`REM30` varchar(100) DEFAULT NULL,
`REM31` varchar(100) DEFAULT NULL,
`REM32` varchar(100) DEFAULT NULL,
`REM33` varchar(100) DEFAULT NULL,
`REM34` varchar(100) DEFAULT NULL,
`REM35` varchar(100) DEFAULT NULL,
`REM36` varchar(100) DEFAULT NULL,
`REM37` varchar(100) DEFAULT NULL,
`REM38` varchar(100) DEFAULT NULL,
`REM39` varchar(100) DEFAULT NULL,
`REM40` varchar(100) DEFAULT NULL,
`REM41` varchar(100) DEFAULT NULL,
`REM42` varchar(100) DEFAULT NULL,
`REM43` varchar(100) DEFAULT NULL,
`REM44` varchar(100) DEFAULT NULL,
`REM45` varchar(100) DEFAULT NULL,
`REM46` varchar(100) DEFAULT NULL,
`REM47` varchar(100) DEFAULT NULL,
`REM48` varchar(100) DEFAULT NULL,
`REM49` varchar(100) DEFAULT NULL,
`multiagent1` varchar(45) DEFAULT '',
`multiagent2` varchar(45) DEFAULT '',
`multiagent3` varchar(45) DEFAULT '',
`multiagent4` varchar(45) DEFAULT '',
`multiagent5` varchar(45) DEFAULT '',
`multiagent6` varchar(45) DEFAULT '',
`multiagent7` varchar(45) DEFAULT '',
`multiagent8` varchar(45) DEFAULT '',
`e_mail` varchar(100) NOT NULL DEFAULT '',
`unlocked` varchar(45) NOT NULL DEFAULT '',
`printstatus` varchar(45) DEFAULT '',
`creditcardtype1` varchar(45) DEFAULT '',
`creditcardtype2` varchar(45) DEFAULT '',
`SONO` text,
`QUONO` text,
`username` varchar(100) DEFAULT '',
`rebateaftertax` char(1) DEFAULT NULL,
`rebateper` double(17,7) NOT NULL DEFAULT '0.0000000',
`rebateamt` double(17,7) NOT NULL DEFAULT '0.0000000',
`termscondition` text,
`cs_pm_cashCD` double(15,5) NOT NULL DEFAULT '0.00000',
`eInvoice_generated` varchar(45) DEFAULT '',
`d_phone2` varchar(100) DEFAULT '',
`d_email` varchar(100) DEFAULT '',
`RQnote` varchar(45) DEFAULT '',
`RQtaxp1` double(15,5) NOT NULL DEFAULT '0.00000',
`roundadj` double(15,5) NOT NULL DEFAULT '0.00000',
`footercurrcode` varchar(45) DEFAULT '',
`footercurrrate` double(16,10) NOT NULL DEFAULT '0.0000000000',
`rem15` varchar(80) DEFAULT '',
`rem16` varchar(80) DEFAULT '',
`rem17` varchar(80) DEFAULT '',
`rem18` varchar(80) DEFAULT '',
`rem19` varchar(80) DEFAULT '',
`rem20` varchar(80) DEFAULT '',
`rem21` varchar(80) DEFAULT '',
`rem22` varchar(80) DEFAULT '',
`rem23` varchar(80) DEFAULT '',
`rem24` varchar(80) DEFAULT '',
`rem25` varchar(80) DEFAULT '',
`postalcode` varchar(35) DEFAULT '',
`d_postalcode` varchar(35) DEFAULT '',
PRIMARY KEY (`TYPE`,`REFNO`,`CUSTNO`,`WOS_DATE`),
KEY `TRANSACTION` (`TYPE`,`REFNO`,`CUSTNO`,`FPERIOD`,`WOS_DATE`,`AGENNO`,`NET`,`TOINV`,`VOID`) USING BTREE,
KEY `CUSTREPORT` (`CUSTNO`,`TYPE`,`REFNO`,`FPERIOD`,`WOS_DATE`,`AGENNO`,`AREA`,`SOURCE`,`NET`,`TOINV`,`ORDER_CL`,`VOID`) USING BTREE,
KEY `AGENTREPORT` (`AGENNO`,`TYPE`,`REFNO`,`CUSTNO`,`FPERIOD`,`WOS_DATE`,`AREA`,`SOURCE`,`NET`,`TOINV`,`VOID`,`NAME`) USING BTREE,
KEY `ENDUSERREPORT` (`VAN`,`TYPE`,`REFNO`,`CUSTNO`,`FPERIOD`,`WOS_DATE`,`AGENNO`,`AREA`,`SOURCE`,`NET`,`TOINV`,`VOID`) USING BTREE
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
>For the slow process of creating sort index which is very slow , Please refer picture below.
http://www.mediafire.com/view/p0ygx7lxyxq4qvu/Capture.PNG