I am doing a quick performance comparison for 10 000 rows on MySQL 8.0.27 between
a table
multiple_cols
with 2 columnsnum INT
andstr CHAR(200)
having each an index,a table
json_indexing
with 1 columncontent JSON
and 2 JSON indexes.
Can you explain why json_indexing
table is 3 times smaller than multiple_cols
?
+---------------+------------+
| Table | Size in MB |
+---------------+------------+
| json_indexing | 2.05 |
| multiple_cols | 6.36 |
+---------------+------------+
// ^results generated with the query:
SELECT
table_name AS `Table`,
round(((data_length + index_length) / 1024 / 1024), 2) `Size in MB`
FROM information_schema.TABLES
WHERE table_name in ('multiple_cols', 'json_indexing') ;
Below are the creation/insertion queries if it can help:
CREATE TABLE json_indexing
(
content JSON
, INDEX json_indexing_content_num_idx ((JSON_VALUE(content, '$.num' RETURNING SIGNED)))
, INDEX json_indexing_content_str_idx ((JSON_VALUE(content, '$.str' RETURNING CHAR(200))))
);
INSERT INTO json_indexing (content)
VALUES ('{ "num": 1, "str": "1" }'), ('{ "num": 2, "str": "2" }') /* ... */ ;
CREATE TABLE multiple_cols
(
num INT
, str CHAR(200)
, INDEX multiple_cols_num_idx (num)
, INDEX multiple_cols_str_idx (str)
);
INSERT INTO multiple_cols (num, str) VALUES (1, '1'), , (2, '2') /* ... */;
Notice that the JSON index for num
returns SIGNED
because it does not accept INT
. That is beyond my understanding for now.
Thank you everyone for your help!
CHAR
toVARCHAR
and see if the ordinary indexes shrink.data_length
is now equals for both tables, butindex_length
is 2.03MB forjson_indexing
and 1.03MB forindex_columns
. I would have expected the other way around: the indexes size should be equals for both tables, and the data size should be bigger for the JSON! Any idea why?