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?
- 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