I have a table with JSON data in MySQL and I’m trying to optimize queries on this data using indexes. Here’s how I’ve set up my table and inserted some data:
<code>CREATE TABLE `table1` (
`data` JSON DEFAULT NULL);
INSERT INTO `table1` (`data`)
VALUES (
'{"metaData": {
"tag": ["info"],
"attributes": [
{"key": "Server", "value": "1"},
{"key": "Level", "value": 53},
{"key": "power", "value": 10}
]
}}'
);
</code>
<code>CREATE TABLE `table1` (
`data` JSON DEFAULT NULL);
INSERT INTO `table1` (`data`)
VALUES (
'{"metaData": {
"tag": ["info"],
"attributes": [
{"key": "Server", "value": "1"},
{"key": "Level", "value": 53},
{"key": "power", "value": 10}
]
}}'
);
</code>
CREATE TABLE `table1` (
`data` JSON DEFAULT NULL);
INSERT INTO `table1` (`data`)
VALUES (
'{"metaData": {
"tag": ["info"],
"attributes": [
{"key": "Server", "value": "1"},
{"key": "Level", "value": 53},
{"key": "power", "value": 10}
]
}}'
);
I attempted to add an index as follows:
<code>ALTER TABLE idx1
ADD INDEX cancel_no_idx ((
CAST(data->>"$.metaData.attributes[*].key" as CHAR(255) ARRAY)
)) USING BTREE;
</code>
<code>ALTER TABLE idx1
ADD INDEX cancel_no_idx ((
CAST(data->>"$.metaData.attributes[*].key" as CHAR(255) ARRAY)
)) USING BTREE;
</code>
ALTER TABLE idx1
ADD INDEX cancel_no_idx ((
CAST(data->>"$.metaData.attributes[*].key" as CHAR(255) ARRAY)
)) USING BTREE;
However, when I run a query to check if the index is being used:
Is there a way to create an index that can be used for the following query?
<code>SELECT *
FROM `table1`
WHERE JSON_CONTAINS(
data->'$.metaData.attributes',
'{"key": "Server", "value": "1"}',
'$'
);
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | table1 | NULL | ALL | NULL | NULL | NULL | NULL | 1 | 100.00 | Using where |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
</code>
<code>SELECT *
FROM `table1`
WHERE JSON_CONTAINS(
data->'$.metaData.attributes',
'{"key": "Server", "value": "1"}',
'$'
);
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | table1 | NULL | ALL | NULL | NULL | NULL | NULL | 1 | 100.00 | Using where |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
</code>
SELECT *
FROM `table1`
WHERE JSON_CONTAINS(
data->'$.metaData.attributes',
'{"key": "Server", "value": "1"}',
'$'
);
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | table1 | NULL | ALL | NULL | NULL | NULL | NULL | 1 | 100.00 | Using where |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
https://sqlfiddle.com/mysql/online-compiler?id=435880da-a637-4cc0-958b-63d5a2602381