I need help for performance this query
SELECT *
FROM transactions
WHERE created_at BETWEEN '2022-08-25 01:03:21' AND '2022-12-13 01:03:21'
AND ((transaction_reason IN ('ORIGINAL','REVERSAL_OF_ADJUSTMENT')
AND type = 'DEPOSIT')
OR (transaction_reason IN ('ADJUSTMENT','REVERSAL_OF_ORIGINAL')
AND type = 'WITHDRAWAL') )
ORDER BY transaction_id ASC
primary key: ID
foreign key: transaction_id
explain
id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
---|---|---|---|---|---|---|---|---|---|---|---|
1 | SIMPLE | transactions | ALL | idx_transactions_dd | 18356060 | 18.00 | Using where; Using filesort |
key is null, my index is idx_transactions_dd (created_at,transaction_reason, type)
any advice?
i need create a new index or modify the query
2
Answers
You need indexes on the columns
(type, created_at)
and(type, transaction_reason)
.Then refactor the query. Instead of using
OR
in your WHERE clause, run two queries andUNION
their respective results:You can’t optimize both
BETWEEN
andIN
in the same WHERE clause. Both are range conditions. An index can support only one range condition (though there is now some support of skip scan range optimization in MySQL 8.0).Since
IN
is equivalent toOR
, further split up the query withUNION
.Change from
UNION
toUNION ALL
assuming there won’t be any dups.This index will be optimal for all 3 Selects:
The UNION:
(I added parentheses to clarify that you wanted the
ORDER BY
to apply to the result of theUNION
, not to the lastSELECT
.)