0

I am doing a quick performance comparison for 10 000 rows on MySQL 8.0.27 between

  • a table multiple_cols with 2 columns num INT and str CHAR(200) having each an index,

  • a table json_indexing with 1 column content 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!

3
  • 1
    Change that CHAR to VARCHAR and see if the ordinary indexes shrink.
    – Rick James
    Jan 13, 2022 at 1:39
  • @RickJames that was it, thank! But I am still curious ... data_length is now equals for both tables, but index_length is 2.03MB for json_indexing and 1.03MB for index_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? Jan 13, 2022 at 9:38
  • Glad `VARCHAR helped. I don't yet understand the details of JSON indexing, so I can't explain your observation.
    – Rick James
    Jan 13, 2022 at 17:28

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Browse other questions tagged or ask your own question.