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)