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 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 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 at 17:28

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.