Slow count (with join) query on MySQL-CodePudding

Well, I just arrived in a project and I do not understand much about SQL tuning.

I saw a process that takes a long time to run and I started to look at some queries this process do.

One of the queries is a count with joins that takes up to 10 minutes to run.

select count(rtvp.id) from rel_transaction_voucher_product rtvp
        join `transaction` t on t.id = rtvp.transaction_id
        join voucher v on v.id = rtvp.voucher_id
        where v.situation_code = 'YYYYY' 
        and t.transaction_type_code = 'XXXX'
        and t.expiration_date < curdate()

Rows of each table in the query:

rel_transaction_voucher_product: 1.7 million
transaction: 1.6 million
voucher: 1.3 million

Explain result:

id|select_type|table|partitions|type  |possible_keys                                                                               |key                                               |key_len|ref                       |rows  |filtered|Extra      |
-- ----------- ----- ---------- ------ -------------------------------------------------------------------------------------------- -------------------------------------------------- ------- -------------------------- ------ -------- ----------- 
 1|SIMPLE     |t    |          |ref   |PRIMARY,transaction_type_code,transaction_id_type_date_IDX                                  |transaction_type_code                             |38     |const                     |815765|   33.33|Using where|
 1|SIMPLE     |rtvp |          |ref   |uk_transid_voucherid_productid,voucher_id,rel_transaction_voucher_product_transaction_id_IDX|rel_transaction_voucher_product_transaction_id_IDX|38     |voucherprd.t.id           |     1|   100.0|           |
 1|SIMPLE     |v    |          |eq_ref|PRIMARY,fk_voucher_situation_code                                                           |PRIMARY                                           |38     |voucherprd.rtvp.voucher_id|     1|   45.46|Using where|

I realize that if I remove and t.expiration_date < curdate() the query returns in about 30 seconds, but this condition is extremely important.

Is there anyway to have this query run faster?

CodePudding user response：

Add an index on the columns being used in the WHERE condition.

ALTER TABLE transaction ADD INDEX (transaction_type_code, expiration_date);

If you already have an index on just transaction_type_code, you won't need it any more, since it's a prefix of this new index.