Question 21572 in Mysql Question posted in
The official MySQL documentation can be found here.

Index mysql key select

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

Tags:

2

Answers


  1. 0

    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).

    Login or Signup to reply.
  2. 0

    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.)

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search