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