How to improve MySQL query that already hits INDEX-CodePudding

How do I optimize a query which already hits Index condition and still take up to 40-80 seconds to process around 400k - 500k records?

CREATE TABLE `merchant_bank_transaction ` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`status` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
`transaction_type` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
`transaction_date` timestamp NULL DEFAULT NULL,
`currency` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
`merchant_id` bigint(20) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `index_primary` (`merchant_id`,`transaction_date`),
ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

I have a table as above with index:

index_primary ('merchant_id', 'transaction_date')

Sample query:

SELECT id,transaction_date, currency, status
FROM merchant_bank_transaction 
WHERE merchant_id=1
transaction_date>='2020-04-01'
transaction_date<='2020-04-30'
AND transaction_type in ('D', 'W')
ORDER BY id desc, transaction_date desc
LIMIT 0, 50;

I ran the above query and it took 54 secs , the explain query is as below:

CodePudding user response：

In given sample query, condition is based on merchant_id, transaction_date and transaction_type. You are using composite index on ('merchant_id', 'transaction_date') but there doesn't seems to be an index on transaction_type which could cause the query to process each and every record.

You should either modify existing composite index and include transaction_type or create a separate index on this single column.

CodePudding user response：

SELECT id,transaction_date, currency, status
FROM merchant_bank_transaction 
WHERE merchant_id = 1 AND transaction_date BETWEEN '2020-04-01' AND '2020-04-30'AND transaction_type in ('D', 'W')
ORDER BY merchant_bank_transaction.id DESC, merchant_bank_transaction.transaction_date DESC
LIMIT 0, 50;

Make sure the "order by" columns are indexed for better performance.