CODEWITHSUNDEEP
mysqlmysql-workbench
user2477936
user2477936

Reputation: 57

Mysql Query taking more time to execute?

SELECT BB.NAME BranchName,VI.NAME Village,COUNT(BAC.CBSACCOUNTNUMBER) "No.Of Accounts",
SUM(BAC.CURRENTBALANCE) SumOfAmount,
SUM(CASE WHEN transactiontype = 'C' THEN amount ELSE 0 END) AS CreditTotal,
SUM(CASE WHEN transactiontype = 'D' THEN amount ELSE 0 END) AS DebitTotal,
SUM(CASE WHEN transactiontype = 'C' THEN amount WHEN transactiontype = 'D' THEN -1 * amount ELSE 0 END) AS CurrentBalance
FROM CUSTOMER CU,APPLICANT AP,ADDRESS AD,VILLAGE VI,BANKBRANCH BB,BANKACCOUNT BAC
LEFT OUTER JOIN accounttransaction ACT ON ACT.BANKACCOUNT_CBSACCOUNTNUMBER=BAC.CBSACCOUNTNUMBER
AND DATE_FORMAT(ACT.TRANDATE,'%Y-%m-%d')<='2013-05-09'
AND DATE_FORMAT(BAC.ACCOUNTOPENINGDATE,'%Y-%m-%d') <'2013-05-09'
AND ACT.BANKACCOUNT_CBSACCOUNTNUMBER IS NOT NULL
WHERE CU.CODE=AP.CUSTOMER_CODE AND BAC.ENTITY='CUSTOMER' AND BAC.ENTITYCODE=CU.CODE
AND AD.ENTITY='APPLICANT' AND AD.ENTITYCODE=AP.CODE
AND AD.VILLAGE_CODE=VI.CODE AND VI.STATE_CODE=AD.STATE_CODE AND VI.DISTRICT_CODE=AD.DISTRICT_CODE
AND VI.BLOCK_CODE=AD.BLOCK_CODE AND VI.PANCHAYAT_CODE=AD.PANCHAYAT_CODE
AND CU.BANKBRANCH_CODE=BB.CODE AND BAC.CBSACCOUNTNUMBER IS NOT NULL AND ACT.TRANSACTIONTYPE IS NOT NULL
GROUP BY BB.NAME,VI.NAME LIMIT 10;

and 

below is my explain plan

id  select_type table   type    possible_keys   key key_len ref rows    Extra
1   SIMPLE  AD  index   ADDRESS_ENTITYCODE  ADDRESS_ENTITYCODE  598 NULL    47234   Using where; Using index; Using temporary; Using filesort
1   SIMPLE  VI  ref PRIMARY PRIMARY 62  fiserveraupgb.AD.VILLAGE_CODE   1   Using where
1   SIMPLE  AP  eq_ref  PRIMARY,AppCodeIndex    PRIMARY 62  fiserveraupgb.AD.ENTITYCODE 1   
1   SIMPLE  BAC ref BANKACCOUNT_ENTITYCODE  BANKACCOUNT_ENTITYCODE  63  fiserveraupgb.AP.CUSTOMER_CODE  1   Using where; Using index
1   SIMPLE  CU  eq_ref  PRIMARY,CustCodeIndex   PRIMARY 62  fiserveraupgb.AP.CUSTOMER_CODE  1   
1   SIMPLE  BB  ref PRIMARY,Bankbranch_CodeName PRIMARY 62  fiserveraupgb.CU.BANKBRANCH_CODE    1   
1   SIMPLE  ACT index   NULL    accounttransaction_sysidindes   280 NULL    22981   Using where; Using index; Using join buffer

Mysql server version 5.5 and I am using mysql workbench below is my query it is taking 13 min to execute, please suggestion the best method I have created the indexes for all the columns which are involved.

Upvotes: 0

Views: 287

Answers (1)

GolezTrol
GolezTrol

Reputation: 116140

You mainly need indexes on columns that are used in joins and in your where clause. Other indexes don't add value for your select statements and slow down your inserts and updates.

In this case, you're using the column values in functions. Due to this the indexes cannot be used efficiently.

An expression like this is very inefficient:

DATE_FORMAT(ACT.TRANDATE,'%Y-%m-%d')<='2013-05-09'

It causes a lot of string conversions, because all TRANDATES are converted to a string representation of their value. These values need to be temporarily stored and are not indexed, so apart from the conversion, any index on ACT.TRANDATE is no longer used. That is probably causing the rather expensive 'Using join buffer' at the end of your explain plan.

Rather convert the string '2013-05-09' to a date value and use this value as a constant in or parameter for your query.

Another thing to do, is create not separate indexes for separate columns, but one index for a group of columns that is used in a where and/or join. For instance this part:

AD.ENTITY = 'APPLICANT' AND 
AD.ENTITYCODE = AP.CODE AND 
AD.VILLAGE_CODE = VI.CODE

Having one index on the columns ENTITY, ENTITYCODE, and VILLAGE_CODE together would be more efficient than having a separate index for each of them. And it may help to include the other columns as well.

And last: If a column or combination of columns is guaranteed to be unique, ad a unique index. It is slightly faster in selects.

A general advise: Don't mix old join syntax with ansi joins. It makes your query hard to read.

These hints (apart from the last one) should speed up your query, but it can still be slow, depending on the amount of data, the hardware and the load.

Upvotes: 1

Related Questions