Not the answer you need?
Register and ask your own question!

MySQL. 8.0.19. Best index to match given query

TMTQuantTMTQuant Current User Role Novice

Hello, thank you in advance.

Im trying to improve my query performance, but stuck in such situation:

Given a table:

CREATE TABLE `user_purchases`
(
    `id`        BIGINT      NOT NULL AUTO_INCREMENT,
    `user_id`   INT         NOT NULL,
    `date`      DATETIME    NOT NULL,
    `status`    VARCHAR(20) NOT NULL,
    `purchase`  BIGINT      NOT NULL,
    `cashback`  BIGINT      NULL DEFAULT -1,
    `coupon_id` INT         NULL,
    PRIMARY KEY (`id`),
    KEY `user_status_key` (`user_id`, `status`),
    KEY `user_key` (`user_id`),
    KEY `status_key` (`status`)
);

Table has many historical data about user purchases

To get all i need i execute such query:

SELECT id FROM user_purchases p
WHERE p.user_id = 1
  AND p.status IN ('PROCESSED', 'FAILED', 'CANCELLED')
  AND p.id < 400
  AND p.coupon_id IS NULL
ORDER BY b.id DESC
LIMIT 10;

But in situations, where user have thousands of purchases in that statuses it takes too long time.

Does i choose best indexes, or i can change them to other to make this query execute faster?

Result for explain query:

select_type - SIMPLE
type - range
select_type - simple
POSSIBLE_KEYS - PRIMARY,user_status_key,status_key,user_key
key - PRIMARY
key_len - 8
ref - null
rows - 8347239
filtered - 0.01
Extra - Using where; Backward index scan

If i use force index (user_status_key) result is better


select_type - SIMPLE
type - range
select_type - simple
POSSIBLE_KEYS - user_status_key
key - user_status_key
key_len - 270
ref - null
rows - 55324
filtered - 10
Extra - Using index condition; Using where; Using filesort


Tagged:
Sign In or Register to comment.

MySQL, InnoDB, MariaDB and MongoDB are trademarks of their respective owners.
Copyright ©2005 - 2020 Percona LLC. All rights reserved.