query speed issue
hello friends,
am need help. i have 2 tables one is account and other is transation
account table have 868 rows. and fetched in 0.0.925s
CREATE TABLE `tblaccounts` (
`ID` int(11) NOT NULL DEFAULT '0',
`AcTitle` varchar(70) CHARACTER SET utf8 DEFAULT NULL,
`Type` varchar(50) CHARACTER SET utf8 DEFAULT NULL,
`Address` varchar(255) CHARACTER SET utf8 DEFAULT NULL,
`City` varchar(25) CHARACTER SET utf8 DEFAULT NULL,
`Climit` decimal(19,4) DEFAULT '0.0000',
`Type1` tinyint(4) DEFAULT '0',
`Type2` tinyint(4) DEFAULT '0',
`obalance` decimal(19,4) DEFAULT '0.0000',
`contact` varchar(50) CHARACTER SET utf8 DEFAULT NULL,
`cRate` decimal(19,4) DEFAULT '0.0000',
`rcRate` decimal(19,4) DEFAULT '0.0000',
`PhoneNo` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
`MobileNo` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
`BankAccount1` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
`BankAccount2` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
`miscInfo1` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
`miscInfo2` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
`miscInfo3` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
`miscInfo4` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
`l_charges` decimal(19,4) DEFAULT NULL,
`m_charges` decimal(19,4) DEFAULT NULL,
PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
and transationac table 152910 rows fetched in 1.4129s.
CREATE TABLE `tbltransactionac` (
`ID` int(11) NOT NULL,
`eDate` date DEFAULT NULL,
`DocNo` int(11) DEFAULT '0',
`Type` varchar(50) CHARACTER SET utf8 DEFAULT NULL,
`AccID` int(11) DEFAULT '0',
`PayDesc` varchar(200) CHARACTER SET utf8 NOT NULL,
`Debit` decimal(19,2) DEFAULT '0.00',
`Credit` decimal(19,2) DEFAULT '0.00',
`EntryGroup` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
PRIMARY KEY (`ID`),
KEY `ttrans_ix_accid_edate_debit_credit` (`AccID`,`eDate`,`Debit`,`Credit`),
CONSTRAINT `AccID` FOREIGN KEY (`AccID`) REFERENCES `tblaccounts` (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
my problem is
my query when am join both tables.
it's take 184.4957s and get 152910
select tbltransactionac.eDate AS eDate,tbltransactionac.DocNo AS DocNo,tbltransactionac.Type AS Type,tbltransactionac.PayDesc AS PayDesc,ifnull(tbltransactionac.Debit,0) AS Debit,ifnull(tbltransactionac.Credit,0) AS Credit,tblaccounts.ID AS ID,tblaccounts.AcTitle AS AcTitle,tblaccounts.Type AS accType,tblaccounts.Address AS Address,tblaccounts.City AS City,tbltransactionac.ID AS tid from (tblaccounts join tbltransactionac on((tblaccounts.ID = tbltransactionac.AccID))) order by tbltransactionac.eDate;
1. in account table have customer record
2. in transactionac table have daily A/C cash transaction
3. in query am want get customer current account balance
can someone help me plz
why speed is slow ???
what am do now ??