MySQL YEARWEEK function is not using index

Question

Given are two tables:

create table table_a
(
    id_k mediumint unsigned   not null,
    id_m tinyint unsigned     not null,
    id_a char(10)             not null,
    v    tinyint unsigned     not null,
    t    enum ('a', 'b', 'c') not null,
    date DATE                 not null,
    unique (id_m, id_a, id_k, t, date),
    index index_1 (id_m, id_a, date),
    index index_date (date)
);

create table table_b
(
    id_a char(10) not null,
    primary key (id_a)
);

Now I want to execute the query:

SELECT YEARWEEK(a.date),
       a.id_a,
       SUM(1 / a.v)
FROM table_a a
         JOIN table_b b on b.id_a = a.id_a
WHERE a.id_m = 1 -- in (1, 2, 3, 4)
  AND a.date BETWEEN 20220901 AND 20221001
GROUP BY YEARWEEK(a.date), a.id_a;

MySQL decides to use the unique-index. Even if I force the index "index_1" there is no speed improvement. I don't know why MySQL can't use the third column "date" from the index "index_1".

Do I have to change the index or are there other solutions?

Edit:

• the index_1 is not unique,
• the table_a has also an index just for the date (thought for my question it is not neccessary)
• all columns cannot be null in the table_a

Answer 1

All(?) function calls and some operators are not sargable -- That is, they cannot use any index.

In some situations, you can get the desired effect by using a GENERATED column and index that.

Every table should have a PRIMARY KEY. Since each of your UNIQUE keys has NULLable column(s), none of them are candidates. Perhaps date is really NOT NULL?

In the case of ENUMs, I usually like to start with an option such as 'unknown' and make the column NOT NULL.

MySQL is unlikely to use any index for the GROUP BY; the Optimizer would prefer to look for an index to handle the WHERE.

I recommend INDEX (id_m, date). When testing just id_m = constant, both columns will be used; else at least the ENUM will be tested. Also having INDEX(date) may help in some situations; I am not sure.

The only way in which an index will be used for YEARYEAR(a.date) is if the index is "covering". And that is beneficial only because it avoids bouncing between the index and the data (separate BTrees).

Answer 2

1. When unique index by (id_m, id_a, date) then the existence of unique index by (id_m, id_a, id_k, t, date) makes no sense because the former index provides uniqueness of the latter index expression.

2. The function usage makes the index unapplicable. You may make this index usable by expanding its expression and making it covering - in this case the server may use it.

3. I'd recommend you to try functional index (if your version allows): index (id_m, id_a, (YEARWEEK(a.date))). Of course this index won't be unique.

4. Also you may create generated column yw INT AS (YEARWEEK(a.date)) and use it in both index and query instead of the function.