| Bug #108194 | When I use JSON_CONTAINS(), the multi-value index is not used | ||
|---|---|---|---|
| Submitted: | 19 Aug 2022 3:22 | Modified: | 19 Aug 2022 11:49 |
| Reporter: | eggs Spiced | Email Updates: | |
| Status: | Can't repeat | Impact on me: | |
| Category: | MySQL Server: JSON | Severity: | S3 (Non-critical) |
| Version: | 8.0.30 | OS: | Any (12.4) |
| Assigned to: | CPU Architecture: | Any (64) | |
| Tags: | Multi-Valued Indexes | ||
[19 Aug 2022 11:49]
MySQL Verification Team
Hi Mr. Spiced, Thank you for your bug report. However, we were not able to repeat your test case. Here it is what we have got: +----+-------------+-----------------------------+------------+------+----------------+------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-----------------------------+------------+------+----------------+------+---------+------+------+----------+-------------+ | 1 | SIMPLE | community_student_info_test | NULL | ALL | tag_json_index | NULL | NULL | NULL | 24 | 54.17 | Using where | +----+-------------+-----------------------------+------------+------+----------------+------+---------+------+------+----------+-------------+ +----+-------------+-----------------------------+------------+------+----------------+------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-----------------------------+------------+------+----------------+------+---------+------+------+----------+-------------+ | 1 | SIMPLE | community_student_info_test | NULL | ALL | tag_json_index | NULL | NULL | NULL | 24 | 58.33 | Using where | +----+-------------+-----------------------------+------------+------+----------------+------+---------+------+------+----------+-------------+ +----+-------------+-----------------------------+------------+------+----------------+------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-----------------------------+------------+------+----------------+------+---------+------+------+----------+-------------+ | 1 | SIMPLE | community_student_info_test | NULL | ALL | tag_json_index | NULL | NULL | NULL | 24 | 62.50 | Using where | +----+-------------+-----------------------------+------------+------+----------------+------+---------+------+------+----------+-------------+ +--+ | +----+-------------+-----------------------------+------------+------+----------------+------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-----------------------------+------------+------+----------------+------+---------+------+------+----------+-------------+ | 1 | SIMPLE | community_student_info_test | NULL | ALL | tag_json_index | NULL | NULL | NULL | 23 | 56.52 | Using where | +----+-------------+-----------------------------+------------+------+----------------+------+---------+------+------+----------+-------------+ +----+-------------+-----------------------------+------------+------+----------------+------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-----------------------------+------------+------+----------------+------+---------+------+------+----------+-------------+ | 1 | SIMPLE | community_student_info_test | NULL | ALL | tag_json_index | NULL | NULL | NULL | 23 | 60.87 | Using where | +----+-------------+-----------------------------+------------+------+----------------+------+---------+------+------+----------+-------------+ +----+-------------+-----------------------------+------------+------+----------------+------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-----------------------------+------------+------+----------------+------+---------+------+------+----------+-------------+ | 1 | SIMPLE | community_student_info_test | NULL | ALL | tag_json_index | NULL | NULL | NULL | 23 | 65.22 | Using where | +----+-------------+-----------------------------+------------+------+----------------+------+---------+------+------+----------+-------------+ continuing ......
[19 Aug 2022 11:51]
MySQL Verification Team
Continued from the previous comment: +----+-------------+-----------------------------+------------+------+----------------+------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-----------------------------+------------+------+----------------+------+---------+------+------+----------+-------------+ | 1 | SIMPLE | community_student_info_test | NULL | ALL | tag_json_index | NULL | NULL | NULL | 16 | 75.00 | Using where | +----+-------------+-----------------------------+------------+------+----------------+------+---------+------+------+----------+-------------+ +----+-------------+-----------------------------+------------+------+----------------+------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-----------------------------+------------+------+----------------+------+---------+------+------+----------+-------------+ | 1 | SIMPLE | community_student_info_test | NULL | ALL | tag_json_index | NULL | NULL | NULL | 16 | 81.25 | Using where | +----+-------------+-----------------------------+------------+------+----------------+------+---------+------+------+----------+-------------+ +----+-------------+-----------------------------+------------+------+----------------+------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-----------------------------+------------+------+----------------+------+---------+------+------+----------+-------------+ | 1 | SIMPLE | community_student_info_test | NULL | ALL | tag_json_index | NULL | NULL | NULL | 16 | 87.50 | Using where | +----+-------------+-----------------------------+------------+------+----------------+------+---------+------+------+----------+-------------+ +----+-------------+-----------------------------+------------+------+----------------+------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-----------------------------+------------+------+----------------+------+---------+------+------+----------+-------------+ | 1 | SIMPLE | community_student_info_test | NULL | ALL | tag_json_index | NULL | NULL | NULL | 13 | 92.31 | Using where | +----+-------------+-----------------------------+------------+------+----------------+------+---------+------+------+----------+-------------+ +----+-------------+-----------------------------+------------+------+----------------+------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-----------------------------+------------+------+----------------+------+---------+------+------+----------+-------------+ | 1 | SIMPLE | community_student_info_test | NULL | ALL | tag_json_index | NULL | NULL | NULL | 13 | 100.00 | Using where | +----+-------------+-----------------------------+------------+------+----------------+------+---------+------+------+----------+-------------+ +----+-------------+-----------------------------+------------+------+----------------+------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-----------------------------+------------+------+----------------+------+---------+------+------+----------+-------------+ | 1 | SIMPLE | community_student_info_test | NULL | ALL | tag_json_index | NULL | NULL | NULL | 13 | 100.00 | Using where | +----+-------------+-----------------------------+------------+------+----------------+------+---------+------+------+----------+-------------+ Can't repeat.
[22 Aug 2022 12:12]
Roy Lyseng
It is likely that the predicate has so low selectivity that the optimizer determines that it is cheaper to perform a table scan than using the index.
Description: I have a table with 500,000 data, and when I query someone tags, the index starts not working when the amount of data is greater than a certain value。 For example： the tag “no_play_in_last_week_qianyanke” when the amount of data is greater than 22, the multi-value index is not used。 the tag “identity_community_entity” when the amount of data is greater than 15, the multi-value index is not used。 the tag “play_5_up_hour” when the amount of data is greater than 12, the multi-value index is not used。 I tested for a month, but did not find the reason, is this a bug How to repeat: CREATE TABLE `community_student_info_test` ( `id` int NOT NULL AUTO_INCREMENT COMMENT '自增id', `tag_bin_json` json DEFAULT NULL, PRIMARY KEY (`id`), KEY `tag_json_index` ((cast(json_extract(`tag_bin_json`,_utf8mb4'$') as char(50) array))) ) ENGINE=InnoDB AUTO_INCREMENT=500169 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='学员信息表'; INSERT INTO `community_student_info_test` (`id`, `tag_bin_json`) VALUES ('1', '[\"activity_user_1_year_use_app\", \"expire_users\", \"free_regist\", \"identity_community_entity\", \"identity_community_nanjing\", \"not_play_in_30_days\", \"not_play_in_7_days\", \"no_play_in_last_week_qianyanke\", \"play_5_up_hour\"]'), ('2', '[\"activity_user_1_year_use_app\", \"expire_users\", \"free_regist\", \"identity_community_lanzhou\", \"identity_community_virtual\", \"not_play_in_30_days\", \"not_play_in_7_days\", \"no_play_in_last_week_qianyanke\", \"play_5_up_hour\"]'), ('3', '[\"identity_community_beijing\", \"identity_community_entity\", \"no_expire_users\", \"play_5_up_hour\", \"play_in_last_week_qianyanke\"]'), ('4', '[\"expire_users\", \"free_regist\", \"identity_community_dalian\", \"identity_community_entity\", \"not_play_in_30_days\", \"not_play_in_7_days\", \"no_play_in_last_week_qianyanke\", \"play_5_up_hour\"]'), ('5', '[\"expire_out_365_up_days\", \"expire_users\", \"free_regist\", \"identity_community_entity\", \"identity_community_shanghai\", \"invite_new_identity\", \"not_play_in_30_days\", \"not_play_in_7_days\", \"no_play_in_last_week_qianyanke\", \"play_5_up_hour\"]'), ('6', '[\"activity_user_1_year_use_app\", \"expire_out_365_up_days\", \"expire_users\", \"free_regist\", \"identity_community_entity\", \"identity_community_shanghai\", \"not_play_in_30_days\", \"not_play_in_7_days\", \"no_play_in_last_week_qianyanke\", \"play_5_up_hour\"]'), ('7', '[\"expire_out_365_up_days\", \"expire_users\", \"free_regist\", \"identity_community_entity\", \"identity_community_nanjing\", \"invite_new_identity\", \"not_play_in_30_days\", \"not_play_in_7_days\", \"no_play_in_last_week_qianyanke\", \"play_5_up_hour\"]'), ('8', '[\"activity_user_1_year_use_app\", \"identity_community_entity\", \"identity_community_shanghai\", \"no_expire_users\", \"play_5_up_hour\", \"play_in_last_week_qianyanke\"]'), ('9', '[\"zombie_user\"]'), ('10', '[\"invite_new_identity\", \"zombie_user\"]'), ('11', '[\"zombie_user\"]'), ('12', '[\"activity_user_1_year_use_app\", \"identity_community_entity\", \"identity_community_shenzhen\", \"not_play_in_30_days\", \"not_play_in_7_days\", \"no_expire_users\", \"no_play_in_last_week_qianyanke\", \"play_5_up_hour\", \"product_sale_channel_douyinhao\", \"product_sale_type_7_card\"]'), ('13', '[\"expire_in_30_90_days\", \"identity_community_entity\", \"identity_community_hangzhou\", \"not_play_in_30_days\", \"not_play_in_7_days\", \"no_expire_users\", \"no_play_in_last_week_qianyanke\", \"play_5_up_hour\"]'), ('14', '[\"expire_users\", \"free_regist\", \"identity_community_entity\", \"identity_community_guangzhou\", \"not_play_in_30_days\", \"not_play_in_7_days\", \"no_play_in_last_week_qianyanke\", \"play_5_up_hour\"]'), ('15', '[\"zombie_user\"]'), ('16', '[\"identity_community_beijing\", \"identity_community_entity\", \"not_play_in_7_days\", \"no_expire_users\", \"no_play_in_last_week_qianyanke\", \"play_5_up_hour\"]'), ('17', '[\"expire_users\", \"free_regist\", \"identity_community_chongqing\", \"identity_community_entity\", \"invite_new_identity\", \"not_play_in_30_days\", \"not_play_in_7_days\", \"no_play_in_last_week_qianyanke\", \"play_5_up_hour\"]'), ('18', '[\"zombie_user\"]'), ('19', '[\"zombie_user\"]'), ('20', '[\"zombie_user\"]'), ('21', '[\"invite_new_identity\", \"zombie_user\"]'), ('22', '[\"expire_out_365_up_days\", \"expire_users\", \"free_regist\", \"identity_community_chongqing\", \"identity_community_entity\", \"invite_new_identity\", \"not_play_in_30_days\", \"not_play_in_7_days\", \"no_play_in_last_week_qianyanke\", \"play_5_up_hour\"]'), ('23', '[\"identity_community_entity\", \"identity_community_hangzhou\", \"no_expire_users\", \"no_play_in_last_week_qianyanke\", \"play_5_up_hour\"]'); mysql> DESC SELECT * FROM community_student_info_test WHERE JSON_CONTAINS(tag_bin_json->'$', JSON_ARRAY("no_play_in_last_week_qianyanke")); +----+-------------+-----------------------------+------------+------+----------------+------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-----------------------------+------------+------+----------------+------+---------+------+------+----------+-------------+ | 1 | SIMPLE | community_student_info_test | NULL | ALL | tag_json_index | NULL | NULL | NULL | 23 | 56.52 | Using where | +----+-------------+-----------------------------+------------+------+----------------+------+---------+------+------+----------+-------------+ 1 row in set, 1 warning (0.00 sec) mysql> DESC SELECT * FROM community_student_info_test WHERE JSON_CONTAINS(tag_bin_json->'$', JSON_ARRAY("identity_community_entity")); +----+-------------+-----------------------------+------------+------+----------------+------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-----------------------------+------------+------+----------------+------+---------+------+------+----------+-------------+ | 1 | SIMPLE | community_student_info_test | NULL | ALL | tag_json_index | NULL | NULL | NULL | 23 | 60.87 | Using where | +----+-------------+-----------------------------+------------+------+----------------+------+---------+------+------+----------+-------------+ 1 row in set, 1 warning (0.00 sec) mysql> DESC SELECT * FROM community_student_info_test WHERE JSON_CONTAINS(tag_bin_json->'$', JSON_ARRAY("play_5_up_hour")); +----+-------------+-----------------------------+------------+------+----------------+------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-----------------------------+------------+------+----------------+------+---------+------+------+----------+-------------+ | 1 | SIMPLE | community_student_info_test | NULL | ALL | tag_json_index | NULL | NULL | NULL | 23 | 65.22 | Using where | +----+-------------+-----------------------------+------------+------+----------------+------+---------+------+------+----------+-------------+ 1 row in set, 1 warning (0.00 sec) mysql> DELETE FROM community_student_info_test WHERE id > 22; Query OK, 1 row affected (0.00 sec) mysql> DESC SELECT * FROM community_student_info_test WHERE JSON_CONTAINS(tag_bin_json->'$', JSON_ARRAY("no_play_in_last_week_qianyanke")); +----+-------------+-----------------------------+------------+-------+----------------+----------------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-----------------------------+------------+-------+----------------+----------------+---------+------+------+----------+-------------+ | 1 | SIMPLE | community_student_info_test | NULL | range | tag_json_index | tag_json_index | 203 | NULL | 12 | 100.00 | Using where | +----+-------------+-----------------------------+------------+-------+----------------+----------------+---------+------+------+----------+-------------+ 1 row in set, 1 warning (0.00 sec) mysql> DESC SELECT * FROM community_student_info_test WHERE JSON_CONTAINS(tag_bin_json->'$', JSON_ARRAY("identity_community_entity")); +----+-------------+-----------------------------+------------+------+----------------+------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-----------------------------+------------+------+----------------+------+---------+------+------+----------+-------------+ | 1 | SIMPLE | community_student_info_test | NULL | ALL | tag_json_index | NULL | NULL | NULL | 22 | 59.09 | Using where | +----+-------------+-----------------------------+------------+------+----------------+------+---------+------+------+----------+-------------+ 1 row in set, 1 warning (0.00 sec) mysql> DESC SELECT * FROM community_student_info_test WHERE JSON_CONTAINS(tag_bin_json->'$', JSON_ARRAY("play_5_up_hour")); +----+-------------+-----------------------------+------------+------+----------------+------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-----------------------------+------------+------+----------------+------+---------+------+------+----------+-------------+ | 1 | SIMPLE | community_student_info_test | NULL | ALL | tag_json_index | NULL | NULL | NULL | 22 | 63.64 | Using where | +----+-------------+-----------------------------+------------+------+----------------+------+---------+------+------+----------+-------------+ 1 row in set, 1 warning (0.00 sec) mysql> DELETE FROM community_student_info_test WHERE id > 15; Query OK, 7 rows affected (0.00 sec) mysql> DESC SELECT * FROM community_student_info_test WHERE JSON_CONTAINS(tag_bin_json->'$', JSON_ARRAY("no_play_in_last_week_qianyanke")); +----+-------------+-----------------------------+------------+-------+----------------+----------------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-----------------------------+------------+-------+----------------+----------------+---------+------+------+----------+-------------+ | 1 | SIMPLE | community_student_info_test | NULL | range | tag_json_index | tag_json_index | 203 | NULL | 9 | 100.00 | Using where | +----+-------------+-----------------------------+------------+-------+----------------+----------------+---------+------+------+----------+-------------+ 1 row in set, 1 warning (0.00 sec) mysql> DESC SELECT * FROM community_student_info_test WHERE JSON_CONTAINS(tag_bin_json->'$', JSON_ARRAY("identity_community_entity")); +----+-------------+-----------------------------+------------+-------+----------------+----------------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-----------------------------+------------+-------+----------------+----------------+---------+------+------+----------+-------------+ | 1 | SIMPLE | community_student_info_test | NULL | range | tag_json_index | tag_json_index | 203 | NULL | 10 | 100.00 | Using where | +----+-------------+-----------------------------+------------+-------+----------------+----------------+---------+------+------+----------+-------------+ 1 row in set, 1 warning (0.00 sec) mysql> DESC SELECT * FROM community_student_info_test WHERE JSON_CONTAINS(tag_bin_json->'$', JSON_ARRAY("play_5_up_hour")); +----+-------------+-----------------------------+------------+------+----------------+------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-----------------------------+------------+------+----------------+------+---------+------+------+----------+-------------+ | 1 | SIMPLE | community_student_info_test | NULL | ALL | tag_json_index | NULL | NULL | NULL | 15 | 73.33 | Using where | +----+-------------+-----------------------------+------------+------+----------------+------+---------+------+------+----------+-------------+ 1 row in set, 1 warning (0.00 sec) mysql> DELETE FROM community_student_info_test WHERE id > 12; Query OK, 3 rows affected (0.02 sec) mysql> DESC SELECT * FROM community_student_info_test WHERE JSON_CONTAINS(tag_bin_json->'$', JSON_ARRAY("no_play_in_last_week_qianyanke")); +----+-------------+-----------------------------+------------+-------+----------------+----------------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-----------------------------+------------+-------+----------------+----------------+---------+------+------+----------+-------------+ | 1 | SIMPLE | community_student_info_test | NULL | range | tag_json_index | tag_json_index | 203 | NULL | 7 | 100.00 | Using where | +----+-------------+-----------------------------+------------+-------+----------------+----------------+---------+------+------+----------+-------------+ 1 row in set, 1 warning (0.00 sec) mysql> DESC SELECT * FROM community_student_info_test WHERE JSON_CONTAINS(tag_bin_json->'$', JSON_ARRAY("identity_community_entity")); +----+-------------+-----------------------------+------------+-------+----------------+----------------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-----------------------------+------------+-------+----------------+----------------+---------+------+------+----------+-------------+ | 1 | SIMPLE | community_student_info_test | NULL | range | tag_json_index | tag_json_index | 203 | NULL | 8 | 100.00 | Using where | +----+-------------+-----------------------------+------------+-------+----------------+----------------+---------+------+------+----------+-------------+ 1 row in set, 1 warning (0.01 sec) mysql> DESC SELECT * FROM community_student_info_test WHERE JSON_CONTAINS(tag_bin_json->'$', JSON_ARRAY("play_5_up_hour")); +----+-------------+-----------------------------+------------+-------+----------------+----------------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-----------------------------+------------+-------+----------------+----------------+---------+------+------+----------+-------------+ | 1 | SIMPLE | community_student_info_test | NULL | range | tag_json_index | tag_json_index | 203 | NULL | 9 | 100.00 | Using where | +----+-------------+-----------------------------+------------+-------+----------------+----------------+---------+------+------+----------+-------------+ 1 row in set, 1 warning (0.00 sec)