Index mysql key select-CodePudding

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

CodePudding user response：

Since IN is equivalent to OR, further split up the query with UNION.

Change from UNION to UNION ALL assuming there won't be any dups.

This index will be optimal for all 3 Selects:

INDEX(type, transaction_reason, created_at)

The UNION:

( SELECT *
    FROM transactions 
    WHERE type = 'DEPOSIT' 
      AND transaction_reason = 'ORIGINAL'
      AND created_at BETWEEN '2022-08-25 01:03:21' AND '2022-12-13 01:03:21'
) UNION ALL
( SELECT *
    FROM transactions 
    WHERE type = 'DEPOSIT' 
      AND transaction_reason = 'REVERSAL_OF_ADJUSTMENT'
      AND created_at BETWEEN '2022-08-25 01:03:21' AND '2022-12-13 01:03:21'
) UNION ALL
    SELECT *
    FROM transactions
    WHERE type = 'WITHDRAWAL'
      AND transaction_reason IN ('ADJUSTMENT', 'REVERSAL_OF_ORIGINAL')
) 
ORDER BY transaction_id ASC;

(I added parentheses to clarify that you wanted the ORDER BY to apply to the result of the UNION, not to the last SELECT.)

CodePudding user response：

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 and UNION their respective results:

SELECT *
FROM transactions 
WHERE type = 'DEPOSIT' 
 AND created_at BETWEEN '2022-08-25 01:03:21' AND '2022-12-13 01:03:21'
 AND transaction_reason IN ('ORIGINAL','REVERSAL_OF_ADJUSTMENT'))
UNION 
SELECT *
FROM transactions
WHERE type = 'WITHDRAWAL'
 AND transaction_reason IN ('ADJUSTMENT','REVERSAL_OF_ORIGINAL') 
ORDER BY transaction_id ASC;

You can't optimize both BETWEEN and IN 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).