Description:
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.
This makes the multi-valued index unusable for our scenario where we always use a subquery to filter on the OrgID column first as shown in our example.
How to repeat:
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)
Description: 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. This makes the multi-valued index unusable for our scenario where we always use a subquery to filter on the OrgID column first as shown in our example. How to repeat: 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)