why is this sql not using index with date type column

Question

mysql:5.7/8.0

table ddl

-- auto-generated definition
create table test_date_index
(
    id  int auto_increment  primary key,
    account_id int not null,
    remark  varchar(10) null,
    cal_date date  null,
    constraint cal_date_index
    unique (cal_date, account_id)
);

in this case not using index

explain
select *
from test_date_index
where (account_id, cal_date) in (
    select account_id, max(cal_date) from test_date_index group by account_id
);

but work in this case

explain
select *
from test_date_index
where (account_id, cal_date) in (
    select account_id, '2022-04-18' from test_date_index group by account_id
)

i think this is because of the type of the cal_date column but i can't find any doc about this

Answer 1

What version are you using? Before 5.7, "row constructors" were not optimized. However, the lack of the optimal index may be the main cause of sluggishness.

For the first query...

Rewrite the "groupwise-max" query thus:

select  b.*
    FROM (  SELECT  account_id, max(cal_date) AS cal_date
            from  test_date_index
            group by  account_id  ) AS a
    JOIN  test_date_index AS b  USING(account_id, cal_date)

Get promote the UNIQUE index to this:

PRIMARY KEY(account_id, cal_date)

with those columns in that order. Specificaly, account_id needs to be first in order to be useful in the "derived" query (subquery) that I used. Also, it tends to be a better way to organize the table.

Your second query shows that it can use your backward index and that 'row constructors' are optimized in the version you are running.