0

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
1
  • A simple way to speed up the query is to remove the JOIN table_b since you are not using anything from table_b. [Or did you over-simplify the query??]
    – Rick James
    Commented Dec 5, 2022 at 22:38

2 Answers 2

0

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).

3
  • My mistake, all columns are not null. Why there is not a primary key in the first place, I don't know. That is what I should working with. The table has already an index(date) (I did not mention it, because I already tried it with the index and it is also slow. What exactly do you mean by if the index is "covering"?
    – Zystrix
    Commented Nov 22, 2022 at 18:43
  • @Zystrix - (the first UNIQUE with all NOT NULLs is promoted to PK. (2) Covering = all columns needed anywhere in the SELECT are in the index. This makes it somewhat faster than the single-column, non-covering index you had.
    – Rick James
    Commented Nov 22, 2022 at 21:18
  • Let's talk about Summary Tables after you deal with my comment about table_b.
    – Rick James
    Commented Dec 5, 2022 at 22:39
0
  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.

1
  • Actually my mistake. The index_1 is not unique. Because the table is large (1+ billion and it keeps growing) I would like to avoid generating new columns/indexes, if not absolutely necessary. These indexes are needed for other queries. Could I increase the speed by grouping by the date in a subquery and after that grouping by yearweek (the temporary table would be much smaller than the potential rows)?
    – Zystrix
    Commented Nov 22, 2022 at 18:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.