Multi-Valued Indexes only takes into account a portion of a whole data.

[Quy Le]

I have a table with one of its column that is of JSON array format, the column has multi-valued index on top of it... but for some reasons, the result returned is very different as compared to non-index result.
My table:
+-------------------+
| character_id_list |
+-------------------+
| [1, 5, 7] |
| [15, 10, 14] |
| [15, 1, 11] |
| [11, 8, 4] |
| [4, 2, 8] |
| [15, 13, 6] |
| [14, 6, 9] |
| [15, 6, 4] |
| [4, 2, 11] |
| [9, 11, 12] |
+-------------------+
Query plan with multi-valued indexes:
+----+-------------+--------------+------------+-------+---------------+----------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------------+------------+-------+---------------+----------+---------+------+------+----------+-------------+
| 1 | SIMPLE | game_players | NULL | range | idx_char | idx_char | 9 | NULL | 878 | 100.00 | Using where |
+----+-------------+--------------+------------+-------+---------------+----------+---------+------+------+----------+-------------+
=> it only takes 878 rows.
//Result (with multi-valued index):
select count(*) from game_players where JSON_CONTAINS(character_id_list,'1','$');
+----------+
| count(*) |
+----------+
| 878 |
+----------+
Query plan without multi-valued indexes:
+----+-------------+--------------+------------+------+---------------+------+---------+------+-------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------------+------------+------+---------------+------+---------+------+-------+----------+-------------+
| 1 | SIMPLE | game_players | NULL | ALL | NULL | NULL | NULL | NULL | 49680 | 100.00 | Using where |
+----+-------------+--------------+------------+------+---------------+------+---------+------+-------+----------+-------------+
=> this one takes up to 49680 rows
//Result (without multi-valued index):
+----------+
| count(*) |
+----------+
| 10106 |
+----------+