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
JOIN table_b
since you are not using anything fromtable_b
. [Or did you over-simplify the query??]