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:
None 
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 3:22] eggs Spiced
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)
[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.