MySQL Multi-Valued Indexes

Question

In the following SQL, Multi-Valued Indexes are generated for the json array.

ALTER TABLE table_name ADD INDEX index_name((CAST(json->'$.field' AS json_type ARRAY))).

ALTER TABLE table_name ADD INDEX index_name((CAST(json->'$.field' AS UNSIGNED ARRAY)));
ALTER TABLE table_name ADD INDEX index_name((CAST(json->'$.field' AS CHAR(255) ARRAY)));

type can be defined to BINARY[(N)] - CHAR[(N)] [charset_info] - DATE - DATETIME - TIME - DECIMAL[(M[,D])] - SIGNED [INTEGER] - UNSIGNED [INTEGER].

Why is the type char(n) instead of varchar(n)?

Is the char(n) here the same as the MySQL type? Will spaces be filled in as well?

Answer 1

Using CHAR over VARCHAR makes all the sense in the world. Why ???

Please note you get better performance reading from and writing to a CHAR rather than a VARCHAR. The tradeoff would be the use of more disk/memory.

While using VARCHAR would use less disk/memory, it would be slower because of the CPU churn needed to calculate and store string lengths in addition to read/write performance on such columns. Such would not be the case with a CHAR field.

I have mentioned CHAR performance over VARCHAR before

• Mar 25, 2011 : Performance implications of MySQL VARCHAR sizes
• May 10, 2011 : What is the performance impact of using CHAR vs VARCHAR on a fixed-size field?