Why does MySQL not use my index in this query?

Question

create table statement:

create table stock_master.turnover_rate_trade
(
    id int auto_increment
        constraint `PRIMARY`
            primary key,
    before_day int null,
    pre_day int null,
    turnover_rate double null ,
    max_rise double null ,
    keep_day int null ,
    code varchar(50) null ,
    date date null ,
    real_keep int null ,
    rate double null ,
    create_time timestamp default CURRENT_TIMESTAMP null,
    update_time timestamp default CURRENT_TIMESTAMP null,
    constraint uindex
        unique (before_day, pre_day, turnover_rate, max_rise, keep_day, code, date)
);

my select statement:

select id,
       before_day,
       pre_day,
       turnover_rate,
       max_rise,
       keep_day,
       code,
       date
       real_keep,
       rate,
       create_time,
       update_time
from turnover_rate_trade
where
   before_day=6 and pre_day =3 and turnover_rate=22.1 and max_rise =7.1 and keep_day=5 and 
   code='100000' and date='2022-02-02'

Execute the plan:

I am not a professional dba, avoid indexes invalidation as much as possible.
It is clearly written in the conditions, but doesn't work.

The table data information:

rows: 2 hundred million
before_day: 5~29
pre_day: 0~4
keep_day: 0~29
turnover_rate: 1~23
max_rise: 1~29

evenly distributed

as suggested:
It took me over an hour to delete and rebuild index.

create index turnover
                on turnover_rate_trade (before_day, pre_day, turnover_rate, max_rise, keep_day, code, date)

Now it works!Can someone explain?

@Bohemian The EXPLAIN output says it's not using the index. — Barmar
– Barmar, Commented Apr 26, 2022 at 1:42
I can't imagine why you would "avoid indexes as much as possible." Indexes make databases work well. If they're unfamiliar, I suggest use-the-index-luke.com as an introduction. — Andy Lester
– Andy Lester, Commented Apr 26, 2022 at 1:59
How many rows are in the table? How many different values are in each column of the indexed columns? — Bohemian
– Bohemian ♦, Commented Apr 26, 2022 at 2:13
Have you tried the most selective column first in the index? I don't know MySql well, but it might be calculating the cost of using the index based on the first few columns/bytes, and it may decide that 200M/(29-5+1) index related reads, followed by the data-row reads may be costlier than just reading all data-rows. Try placing the date first, if it is the column with most unique values. — tinazmu
– tinazmu, Commented Apr 26, 2022 at 2:53

Rick James · Accepted Answer · 2022-04-26 03:52:32Z

FLOAT or DOUBLE are approximate values, stored in binary. 22.1 is a decimal value that cannot be stored exactly in any DOUBLE. It is risky to test with = with a DOUBLE on one side and a non-integral decimal number on the other side.

I understand that this fails to explain why the Index was (according to the EXPLAIN) not used. But let's fix the comparison first.

Plan A: Use some form of range test. (This will definitely mess up using the index.)

Plan B: switch the column to DECIMAL. (Such an ALTER will take a long time.)

You possibly have a typo here:

   code,
   date        -- perhaps you wanted a comma?
   real_keep,

Another issue... You have not specified a PRIMARY KEY (and that UNIQUE key cannot be promoted to PK due to nullability of most of its columns). A PK is really needed for all tables.

Suggest making columns NOT NULL where appropriate.

Suggest adding an explicit PK.

(Again, I can't explain the failure to your your index.)

Collectives™ on Stack Overflow

Why does MySQL not use my index in this query?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related