MySql update does not use index

The MySql command below does not use the index in update.
In where, we make decisions in the order of column FlagSer and PriceUpdateTimeSer.
The index fflag_price_idx は is the composite index of the column FlagSer, PriceUpdateTimeSer.
The order of the columns also matches, so the index should normally be used, but it is not.
I forced the force index to use flag_price_idx, but the result did not change.
Why doesn't this index work?
I would appreciate it if you could give me guidance.

update shop_his
set FlagSer= FlagSer&(~128)
where FlagSer&128&
PriceUpdateTimeSer<638117998611507506;

+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| id | select_type | table | partitions | type | posible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+----------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
|  1 | UPDATE | shop_his | NULL | index | NULL | PRIMARY | 1026 | NULL | 4818 | 100.00 | Using where |
+----+-------------+----------+------------+-------+---------------+---------+---------+------+------+----------+-------------+

+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Table | Non_unique | Key_name | Seq_in_index | Columnn_name | Collation | Cardinity | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+----------+------------+----------------+--------------+--------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| shop_his | 0 | PRIMARY | 1 | URL | A | 5335 | NULL | NULL | | BTREE | | YES | NULL |
| shop_his | 1 | get_next_data | 1 | FlagSer | A | 27 | NULL | NULL | | BTREE | | YES | NULL |
| shop_his|1|get_next_data|2|LastExeTimeSer|A|5016|NULL|NULL||BTREE||YES|NULL|
| shop_his | 1 | name | 1 | Name | A | 3386 | NULL | NULL | | BTREE | | YES | NULL |
| shop_his | 1 | priority_flag | 1 | FlagSer | A | 13 | NULL | NULL | | BTREE | | YES | NULL |
| shop_his | 1 | priority_flag | 2 | PriceUpdateTimeSer | A | 5307 | NULL | NULL | | BTREE | | YES | NULL |
| shop_his | 1 | flag_url | 1 | FlagSer | A | 36 | NULL | NULL | | BTREE | | YES | NULL |
| shop_his | 1 | flag_url | 2 | URL | A | 5186 | NULL | NULL | | BTREE | | YES | NULL |
| shop_his | 1 | url_flag | 1 | URL | A | 5152 | NULL | NULL | | BTREE | | YES | NULL |
| shop_his | 1 | url_flag | 2 | FlagSer | A | 4852 | NULL | NULL | | BTREE | | YES | NULL |
| shop_his|1|version_time|1|VersionSer|A|1|NULL|NULL||BTREE||YES|NULL|
| shop_his|1|version_time|2|LastExeTimeSer|A|5074|NULL|NULL||BTREE||YES|NULL|
| shop_his | 1 | dhash_flag | 1 | FlagSer | A | 34 | NULL | NULL | | BTREE | | YES | NULL |
| shop_his | 1 | dhash_flag | 2 | DHashUpdateTimeSer | A | 3780 | NULL | NULL | | BTREE | | YES | NULL |
| shop_his | 1 | flag_idx | 1 | FlagSer | A | 39 | NULL | NULL | | BTREE | | YES | NULL |
| shop_his | 1 | flag_price_idx | 1 | FlagSer | A | 20 | NULL | NULL | | BTREE | | YES | NULL |
| shop_his | 1 | flag_price_idx | 2 | PriceUpdateTimeSer | A | 4570 | NULL | NULL | | BTREE | | YES | NULL |
+----------+------------+----------------+--------------+--------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+

+----------------------------------------------------------------------------------------------------------------------------------------
| Field | Type | Null | Key | Default | Extra |
+--------------------+---------------------+------+-----+---------+-------+
| Name | varchar (512) | NO | MUL | NUL | |
| URL | varbinary (1024) | NO | PRI | NULL | |
| LastExeTimeSer | bigint(20) | NO | | NULL | |
| FlagSer | int(10) unsigned | NO | MUL | NUL | |
| SiteName|varchar(512)|NO||NULL|
| PriceUpdateTimeSer | bigint(20) | NO | | NULL | |
| VersionSer | bigint(19) unsigned | NO | MUL | NUL | |
| GrossDataNum | int(11) | YES | | NULL | |
| DHashUpdateTimeSer | bigint(20) | NO | | NULL | |
| LastSuccessTimeSer|bigint(20)|NO||NULL||
+--------------------+---------------------+------+-----+---------+-------+

8.0.18

mysql

2023-02-13 12:29

1 Answers

Basically, the index is not used when you do the following:

○SELECT* FROM SomeTable WHERE col_1=10 AND col_2=100 AND col_3=500;
○SELECT* FROM SomeTable WHERE col_1=10 AND col_2=100;
×SELECT* FROM SomeTable WHERE col_1=10 AND col_3=500;
×SELECT* FROM SomeTable WHERE col_2=100 AND col_3=500;
×SELECT* FROM SomeTable WHERE col_2=100 AND col_1=10;

It's a very old description, but
under Mysql, whether or not the index works after bit operation. There is an answer that it doesn't work, so it won't work if the specifications haven't changed.
https://forums.mysql.com/read.php?24,35318,35318#msg-35318

2023-02-13 22:46

If you have any answers or tips

Popular Tags

python x 4647

android x 1593

java x 1494

javascript x 1427

c x 927

c++ x 878

ruby-on-rails x 696

php x 692

python3 x 685

html x 656