Details
-
Task
-
Status: Open (View Workflow)
-
Major
-
Resolution: Unresolved
-
Q3/2026 Server Development
Description
How ARRAY indexes work in MySQL. The goal is aiding the design of MDEV-40168 (actually MDEV-40777) in terms of compatibility and performance of the MariaDB MVI-on-fulltext.
Contents
1. Index definition
|
2. Supported datatypes
|
3. Unique indexes
|
4. Sargable predicates
|
4.1 JSON_OVERLAPS
|
4.2 JSON_CONTAINS
|
5. Producing query plans from the WHERE
|
5.1 Ranges are not joined together
|
5.2 Type-erasure: false positives and false negatives
|
6. Multi-part keys
|
1. Index definition
Index is defined over CAST ... AS datatype ARRAY like so:
create index idx1 on t1 ((cast(a->'$.arr' as unsigned array))); |
create index idx1 on t1 ((cast (json_extract(a, '$.arr') as unsigned array))); |
2. Supported datatypes
See the function validate_cast_type_and_extract_length. Here is the list of all supported datatypes
- SIGNED [INT]
- UNSIGNED [INT]
- DATE
- TIME / TIME(p)
- DATETIME / DATETIME(p)
- DECIMAL / DECIMAL(m[,d])
- CHAR(n) - length mandatory, n <= 512; charset must be unspecified (my_charset_bin)
- BINARY(n) - length mandatory, n <= 512
If the extracted value is of string datatype, it will have binary collation for BINARY(n) and my_charset_utf8mb4_0900_bin ("utf8mb4_0900_bin") for CHAR(n) and will be compared with memcpy
Attempt to insert a row where an array element evaluates to a JSON null or JSON array or JSON object results in an error.
Attempt to insert a row where an array element can't be parsed to a value of the target datatype results in a query error.
For example:
mysql> create table t1 (c int, j json, key idx ((CAST(j->'$.tags' AS UNSIGNED ARRAY))))engine=innodb;
|
Query OK, 0 rows affected (0.855 sec)
|
|
|
mysql> insert into t1 values (1, '{"tags": ["1", "34567"]}');
|
Query OK, 1 row affected (0.026 sec)
|
|
|
mysql> select cast(json_extract('[1, 42, "3"]', '$[2]') as unsigned);
|
+--------------------------------------------------------+
|
| cast(json_extract('[1, 42, "3"]', '$[2]') as unsigned) |
|
+--------------------------------------------------------+
|
| 3 |
|
+--------------------------------------------------------+
|
1 row in set (0.001 sec)
|
|
|
|
|
mysql> insert into t1 values (1, '{"tags": ["1", "abcde", "34567"]}');
|
ERROR 3903 (22018): Invalid JSON value for CAST for functional index 'idx'.
|
|
|
mysql> select cast(json_extract('["1", "abcde", "34567"]', '$[1]') as unsigned);
|
+-------------------------------------------------------------------+
|
| cast(json_extract('["1", "abcde", "34567"]', '$[1]') as unsigned) |
|
+-------------------------------------------------------------------+
|
| 0 |
|
+-------------------------------------------------------------------+
|
1 row in set, 1 warning (0.001 sec)
|
|
|
mysql> show warnings;
|
+---------+------+--------------------------------------------------------------------------+
|
| Level | Code | Message |
|
+---------+------+--------------------------------------------------------------------------+
|
| Warning | 3156 | Invalid JSON value for CAST to INTEGER from column json_extract at row 1 |
|
+---------+------+--------------------------------------------------------------------------+
|
1 row in set (0.001 sec)
|
|
3. Unique indexes
Indexes using ARRAY may be UNIQUE. Attempt to insert a row where one (or more) of array elements overlap already existing row will fail.
Array elements in the same row may have duplicates.
CREATE TABLE mvi_uniq ( |
id INT AUTO_INCREMENT PRIMARY KEY, |
attributes JSON,
|
attr1 VARCHAR(32), |
UNIQUE INDEX idx_attributes ((CAST(attributes->'$.tags' AS BINARY(32) ARRAY))), |
INDEX idx_attr1 (attr1) |
);
|
mysql> insert into mvi_uniq (attributes, attr1) values ('{ "tags": ["val1-10", "val1-11"] }', 'val1-11'); |
Query OK, 1 row affected (0.036 sec)
|
|
|
mysql> insert into mvi_uniq (attributes, attr1) values ('{ "tags": ["val1-99999949", "val1-10"] }', 'val1-10'); |
ERROR 1062 (23000): Duplicate entry '["base64:type15:dmFsMS0xMA==", "base64:type15:dmFsMS05OTk5OTk0OQ' for key 'mvi_uniq.idx_attributes' |
4. Sargable predicates
These predicates are sargable:
- JSON_OVERLAPS(json_doc1, json_doc2)
- JSON_CONTAINS(json_doc, val[, path])
- value MEMBER OF (json_array)
4.1 JSON_OVERLAPS
One of the arguments must point to the array being indexed. Say we have key idx ((CAST(attributes->'$.tags[]' AS CHAR(6) ARRAY))), this is sargable:
WHERE JSON_OVERLAPS(attributes->'$.tags', '["val1-0"]') |
while this is not:
WHERE JSON_OVERLAPS(attributes, '{"tags":["val1-0"]}') ; |
The path must point to the array:
where JSON_OVERLAPS('["val1-0", null]', attributes->'$.tags'); -- COND1 |
referring to "all array elements"
where JSON_OVERLAPS('["val1-0", null]', attributes->'$.tags[*]'); -- COND2 |
is equivalent for tags that are arrays, but is not handled by the optimizer. However, if the key is key idx ((CAST(attributes->'$.tags[*]' AS CHAR(6) ARRAY))), then the situation is flipped - COND1 results in full table scan but COND2 is sargable.
4.2 JSON_CONTAINS
Examples of sargable conditions
explain select * from mvi1 where json_contains(attributes->'$.tags', '"val1-0"); |
explain select * from mvi1 where json_contains(attributes->'$.tags', '["val1-1","val1-2"]'); |
Note that
where json_contains(attributes, '"val1-0"', '$.tags'); |
is not sargable even if it is identical. It appears that mysql only checks the first two arguments here and ignore the third, for example JSON_CONTAINS(attributes->'$.tags', '"val1-0"', '$.what[].ever') is sargable!
5. Producing query plans from the WHERE
Access path construction is integrated into the range optimizer. For query like
explain
|
SELECT id FROM mvi_benchmark WHERE |
'val1-10' MEMBER OF(attributes->'$.tags') |
or
|
'val1-12' MEMBER OF(attributes->'$.tags'); |
special kinds of ranges will be produced
"range_scan_alternatives": [ |
{
|
"index": "idx_attributes", |
"ranges": [ |
"0x070076616c312d3132000...00 MEMBER OF (json_extract(attributes,_utf8mb4'$.tags'))", |
"0x070076616c312d3130000...00 MEMBER OF (json_extract(attributes,_utf8mb4'$.tags'))" |
],
|
Range scans on these ranges can participate in index_merge.
explain
|
SELECT id FROM mvi_benchmark WHERE |
'val1-10' MEMBER OF(attributes->'$.tags') |
or
|
attr1='val1-11'; |
5.1 Ranges are not joined together
ORed conditions are not joined together:
explain
|
select * from mvi1 |
where
|
'val1' MEMBER OF(attributes->'$.tags') or |
'val1' MEMBER OF(attributes->'$.tags') ; |
gives
"ranges": [ |
"'val1' MEMBER OF (json_extract(attributes,_utf8mb4'$.tags'))", |
"'val1' MEMBER OF (json_extract(attributes,_utf8mb4'$.tags'))" |
],
|
for AND-ed conditions
where
|
'val1' MEMBER OF(attributes->'$.tags') AND |
'val2' MEMBER OF(attributes->'$.tags') ; |
it just picks the first one
"ranges": [ |
"'val2' MEMBER OF (json_extract(attributes,_utf8mb4'$.tags'))" |
],
|
5.2 Type-erasure: false positives and false negatives
It appears that mysql casts every array element to the declared type, indexes that, and the optimizer casts the query constant the same way.
This causes the following bugs:
mysql> create table t1 (c int, j json, key idx ((CAST(j->'$.tags' AS CHAR(6) ARRAY))))engine=innodb;
|
Query OK, 0 rows affected (0.232 sec)
|
|
|
mysql> insert into t1 values (1, '{"tags": [1, "abcde", "34567", "", 34567]}');
|
Query OK, 1 row affected (0.046 sec)
|
|
|
mysql> select * from t1 where json_contains(j->'$.tags', '1.0');
|
Empty set (0.012 sec)
|
|
|
mysql> alter table t1 drop index idx;
|
Query OK, 0 rows affected (0.155 sec)
|
Records: 0 Duplicates: 0 Warnings: 0
|
|
|
mysql> select * from t1 where json_contains(j->'$.tags', '1.0');
|
+------+--------------------------------------------+
|
| c | j |
|
+------+--------------------------------------------+
|
| 1 | {"tags": [1, "abcde", "34567", "", 34567]} |
|
+------+--------------------------------------------+
|
1 row in set (0.011 sec)
|
|
|
mysql> alter table t1 add index idx ((CAST(j->'$.tags' AS CHAR(6) ARRAY)));
|
Query OK, 0 rows affected (0.162 sec)
|
Records: 0 Duplicates: 0 Warnings: 0
|
|
|
mysql> select * from t1 where json_contains(j->'$.tags', '"1"');
|
+------+--------------------------------------------+
|
| c | j |
|
+------+--------------------------------------------+
|
| 1 | {"tags": [1, "abcde", "34567", "", 34567]} |
|
+------+--------------------------------------------+
|
1 row in set (0.003 sec)
|
|
|
mysql> alter table t1 drop index idx;
|
Query OK, 0 rows affected (0.793 sec)
|
Records: 0 Duplicates: 0 Warnings: 0
|
|
|
mysql> select * from t1 where json_contains(j->'$.tags', '"1"');
|
Empty set (0.019 sec)
|
In the first example (with '1.0'), presumably the array element 1 is converted from a number to a CHAR(6) before being added to the index, and the query constant number 1.0 is also converted to a CHAR(6) and index lookup results in a false negative (string '1.0' != string '1'). In the second, there's an index hit but it fails to recheck.
6. Multi-part keys
Since processing is integrated into the range optimizer, it is possible to support multi-part keys and ranges, where one key part is an ARRAY.
MySQL requires that in the index, only one keypart uses ARRAY indexing.
TODO: How important this is?
TODO: Can we skip supporting this in the first milestone?
Attachments
Issue Links
- is part of
-
MDEV-40168 Indexes over JSON data based on Fulltext index
-
- In Progress
-