Query Performance Issue - for select statement even with indexes on the table with more than 20 Trillion records

Question

we have three table from which the records needs to be picked from three table namely Accounts having 162161 records Account_Types 6 records Transactions having 10761247 records

Below is the tables structure

TRANSACTIONS

id bigint(20) unsigned NO PRI auto_increment transfer_number bigint(20) unsigned YES MUL
debit_credit varchar(255) NO MUL
fund_type varchar(255) NO MUL
fund_id bigint(20) unsigned YES MUL
currency varchar(255) NO
amount decimal(20,2) NO MUL 0
description text NO
other_type varchar(255) NO
other_id bigint(20) unsigned YES
transaction_type varchar(255) NO
accounts_receivable int(1) unsigned NO 0
transaction_status varchar(255) NO MUL
creation_date datetime NO 0000-00-00 00:00:00 execution_date datetime NO MUL 0000-00-00 00:00:00 api int(1) YES
claim_id char(8) YES

ACCOUNTS

id bigint(20) unsigned NO PRI auto_increment user_id bigint(20) unsigned YES MUL
account_number varchar(255) NO UNI
type_id bigint(20) unsigned YES MUL
description varchar(255) NO
commission_acc_id bigint(20) unsigned YES MUL
allow_debit varchar(255) NO
allow_credit varchar(255) NO
account_status varchar(255) NO MUL
creation_date datetime NO 0000-00-00 00:00:00

ACCOUNT_TYPES

id bigint(20) unsigned NO PRI auto_increment description varchar(255) NO MUL
currency varchar(255) NO MUL
monthly_fee_amount double NO 0
monthly_fee_description varchar(255) NO
yearly_fee_amount double NO 0
yearly_fee_description varchar(255) NO
generate_interests varchar(255) NO
interest_rate double NO 0
interest_payout_period varchar(255) NO
interest_payout_day char(2) NO
interest_payout_month char(2) NO
interest_payout_hour char(2) NO
interest_based_on varchar(255) NO
interest_based_on_period varchar(255) NO
interest_minimum_balance double NO 0
generate_commissions varchar(255) NO
commission_rate double NO 0
commission_payout_period varchar(255) NO
commission_payout_day char(2) NO
commission_payout_month char(2) NO
commission_payout_hour char(2) NO
commission_based_on varchar(255) NO
commission_based_on_period varchar(255) NO
commission_minimum_balance double NO 0

Below is the query we are using the below query :

SELECT SUM(t.amount) AS total_credit 
FROM    account_types at, 
        accounts a, 
        transactions t 
WHERE   at.currency          = var_currency 
AND     at.id                = a.type_id 
AND     (a.account_status    = 'active' 
        OR a.account_status  = 'blocked')
AND     a.id                 = t.fund_id 
AND     t.debit_credit       = var_debit_credit 
AND     t.fund_type          = 'account'
AND     t.transaction_status = 'executed';

which is taking 20 mins and more to get the output.

Pre-requsite from management - since it is a table of transactions we cannot archive the table we need the records to calculate the sum and not hardware changes allowed. There are indexes on the table is also present. Represented as MUL in description.

Can you please post an EXPLAIN, and tell us what indexes exist? — Neville Kuyt
These are the indexes on the tables TRANSACTIONS id debit_credit fund_type amount transaction_status ACCOUNTS id type_id commission_acc_id account_status ACCOUNT_TYPES id currency — shashi27
Where does var_currency and var_debit_credit refer to? Are they constant? — Benvorth
Are there other account-Status than active or blocked? How many more? — Benvorth
Without an EXPLAIN, and the exact indexes you have already, we can only guess... — Neville Kuyt

Willem Renzema Willem Renzema · Accepted Answer · 2014-11-08T20:37:09

First things first, try adding an index on all the columns you need from the transaction table.

ALTER TABLE transaction
ADD INDEX `DebCredFundTypeTransStatus`
(`fund_id`,`debit_credit`,`fund_type`,`transaction_status`,`amount`)

This will reduce the number of lookups to this table to just this index, without having to go to the table itself for the data. It is important that amount is the rightmost part of the index.

Since a.account_status and at.currency are the only columns in their respective tables that you need to access (other than the primary key, which is included in the rightmost part of all indexes) then you should be fine with those.

The next big bottleneck would be the OR clause. Since you aren't ordering your results, we can easily slap a UNION ALL and just run it twice for each desired value of account_status.

SELECT SUM(t.amount) AS total_credit 
FROM    account_types at, 
        accounts a, 
        transactions t 
WHERE   at.currency          = var_currency 
AND     at.id                = a.type_id 
AND     a.account_status    = 'active'
AND     a.id                 = t.fund_id 
AND     t.debit_credit       = var_debit_credit 
AND     t.fund_type          = 'account'
AND     t.transaction_status = 'executed'

UNION ALL

SELECT SUM(t.amount) AS total_credit 
FROM    account_types at, 
        accounts a, 
        transactions t 
WHERE   at.currency          = var_currency 
AND     at.id                = a.type_id 
AND     a.account_status  = 'blocked'
AND     a.id                 = t.fund_id 
AND     t.debit_credit       = var_debit_credit 
AND     t.fund_type          = 'account'
AND     t.transaction_status = 'executed';

Those are probably the simplest and most productive changes. Try them and see if things have improved sufficiently. If not, there are other optimizations that can be applied.

Query Performance Issue - for select statement even with indexes on the table with more than 20 Trillion records

1 Answers