CODEWITHSUNDEEP
mysqlsqlindexing
zhanzezhu
zhanzezhu

Reputation: 121

Why does MySQL not use my index in this query?

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: enter image description here

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? enter image description here

Upvotes: 0

Views: 95

Answers (1)

Rick James
Rick James

Reputation: 142208

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

Upvotes: 2

Related Questions