MySQL Forums
Forum List  »  Optimizer & Parser

Multi-valued index is not in possible keys when there is a subquery
Posted by: Jinlin Yang
Date: July 22, 2021 07:18PM

If we table contains some column based index and also some multi-valued index for a json column, then the query optimizer behaves differently depending on if subquery is used.

Specifically when a subquery is used then the multi-valued index is NOT listed as a possible keys in the output of the explain command. However, if we change the query not to use any subquery, then the multi-valued index is among the possible keys.

Does anyone know how to work around this issue?

I have reported a bug at https://bugs.mysql.com/bug.php?id=104388.

Below is the steps to reproduce the problem.

mysql> STATUS;
--------------
mysql Ver 8.0.25 for macos11.3 on x86_64 (Homebrew)

Connection id: 18
Current database: myapp
Current user: root@localhost
SSL: Not in use
Current pager: stdout
Using outfile: ''
Using delimiter: ;
Server version: 8.0.25 Homebrew
Protocol version: 10
Connection: Localhost via UNIX socket
Server characterset: utf8mb4
Db characterset: utf8mb4
Client characterset: utf8mb4
Conn. characterset: utf8mb4
UNIX socket: /tmp/mysql.sock
Binary data as: Hexadecimal
Uptime: 41 min 5 sec

Threads: 8 Questions: 958 Slow queries: 0 Opens: 1005 Flush tables: 3 Open tables: 912 Queries per second avg: 0.388
--------------

mysql>
mysql> DROP TABLE IF EXISTS test_json_index;
Query OK, 0 rows affected (0.01 sec)

mysql> CREATE TABLE test_json_index
-> (
-> id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
-> OrgID INT NOT NULL,
-> BinID INT NOT NULL,
-> DataBlob json NOT NULL
-> );
Query OK, 0 rows affected (0.01 sec)

mysql> ALTER TABLE test_json_index
-> ADD INDEX (OrgID),
-> ADD INDEX (BinID),
-> ADD INDEX indexA (( CAST(DataBlob->'$.a' AS UNSIGNED INTEGER ARRAY) )),
-> ADD INDEX indexB (( CAST(DataBlob->'$.b' AS UNSIGNED ) ));
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql>
mysql> INSERT INTO test_json_index (OrgID, BinID, DataBlob)
-> VALUES(
-> 1, 1, "{\"a\":[1, 2, 3], \"b\": 1}"
-> );
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO test_json_index (OrgID, BinID, DataBlob)
-> VALUES(
-> 2, 2, "{\"a\":[4, 5, 6], \"b\": 2}"
-> );
Query OK, 1 row affected (0.00 sec)

mysql>
mysql> SHOW INDEX FROM test_json_index;
+-----------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------------------------------------------------------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+-----------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------------------------------------------------------------+
| test_json_index | 0 | PRIMARY | 1 | id | A | 1 | NULL | NULL | | BTREE | | | YES | NULL |
| test_json_index | 1 | OrgID | 1 | OrgID | A | 1 | NULL | NULL | | BTREE | | | YES | NULL |
| test_json_index | 1 | BinID | 1 | BinID | A | 1 | NULL | NULL | | BTREE | | | YES | NULL |
| test_json_index | 1 | indexA | 1 | NULL | A | 1 | NULL | NULL | YES | BTREE | | | YES | cast(json_extract(`DataBlob`,_utf8mb4\'$.a\') as unsigned array) |
| test_json_index | 1 | indexB | 1 | NULL | A | 1 | NULL | NULL | YES | BTREE | | | YES | cast(json_extract(`DataBlob`,_utf8mb4\'$.b\') as unsigned) |
+-----------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------------------------------------------------------------+
5 rows in set (0.00 sec)

mysql>
mysql> -- Filter on OrgID and DataBlob.a with a subquery, why is indexA missing in possible keys?
mysql> EXPLAIN SELECT *
-> FROM (SELECT * from test_json_index where OrgID = 1) as tmp
-> WHERE 1 member of(tmp.DataBlob->'$.a');
+----+-------------+-----------------+------------+------+---------------+-------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-----------------+------------+------+---------------+-------+---------+-------+------+----------+-------------+
| 1 | SIMPLE | test_json_index | NULL | ref | OrgID | OrgID | 4 | const | 1 | 100.00 | Using where |
+----+-------------+-----------------+------------+------+---------------+-------+---------+-------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

mysql>
mysql> -- Filter on OrgID and DataBlob.a without a subquery
mysql> EXPLAIN SELECT *
-> FROM test_json_index
-> WHERE 1 member of(DataBlob->'$.a') AND OrgID = 1;
+----+-------------+-----------------+------------+------+---------------+-------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-----------------+------------+------+---------------+-------+---------+-------+------+----------+-------------+
| 1 | SIMPLE | test_json_index | NULL | ref | OrgID,indexA | OrgID | 4 | const | 1 | 100.00 | Using where |
+----+-------------+-----------------+------------+------+---------------+-------+---------+-------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

mysql>
mysql> -- Filter on OrgID and DataBlob.b with a subquery, why is indexB missing in possible keys?
mysql> EXPLAIN SELECT *
-> FROM (SELECT * from test_json_index where OrgID = 1) as tmp
-> WHERE 1 = CAST(DataBlob->'$.b' AS UNSIGNED);
+----+-------------+-----------------+------------+------+---------------+-------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-----------------+------------+------+---------------+-------+---------+-------+------+----------+-------------+
| 1 | SIMPLE | test_json_index | NULL | ref | OrgID | OrgID | 4 | const | 1 | 100.00 | Using where |
+----+-------------+-----------------+------------+------+---------------+-------+---------+-------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

mysql>
mysql> -- Filter on OrgID and DataBlob.b without a subquery
mysql> EXPLAIN SELECT *
-> FROM test_json_index
-> WHERE 1 = CAST(DataBlob->'$.b' AS UNSIGNED) AND OrgID = 1;
+----+-------------+-----------------+------------+------+---------------+-------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-----------------+------------+------+---------------+-------+---------+-------+------+----------+-------------+
| 1 | SIMPLE | test_json_index | NULL | ref | OrgID,indexB | OrgID | 4 | const | 1 | 50.00 | Using where |
+----+-------------+-----------------+------------+------+---------------+-------+---------+-------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

mysql>
mysql> -- Filter on OrgID and BinID with a subquery, index on BinID is in possible keys
mysql> EXPLAIN SELECT *
-> FROM (SELECT * from test_json_index where OrgID = 1) as tmp
-> WHERE 1 = tmp.BinID;
+----+-------------+-----------------+------------+------+---------------+-------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-----------------+------------+------+---------------+-------+---------+-------+------+----------+-------------+
| 1 | SIMPLE | test_json_index | NULL | ref | OrgID,BinID | OrgID | 4 | const | 1 | 50.00 | Using where |
+----+-------------+-----------------+------------+------+---------------+-------+---------+-------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

mysql>
mysql> -- Filter on OrgID and BinID without a subquery
mysql> EXPLAIN SELECT *
-> FROM test_json_index
-> WHERE 1 = BinID AND OrgID = 1;
+----+-------------+-----------------+------------+------+---------------+-------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-----------------+------------+------+---------------+-------+---------+-------+------+----------+-------------+
| 1 | SIMPLE | test_json_index | NULL | ref | OrgID,BinID | OrgID | 4 | const | 1 | 50.00 | Using where |
+----+-------------+-----------------+------------+------+---------------+-------+---------+-------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

Options: ReplyQuote


Subject
Views
Written By
Posted
Multi-valued index is not in possible keys when there is a subquery
75
July 22, 2021 07:18PM


Sorry, only registered users may post in this forum.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.