1

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?

1 Answer 1

0

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

5
  • Is each element in the char(n) array equivalent to char?Are spaces filled when length is not enough? Jan 3 at 2:40
  • There will usually be some CPU load to calculate string lengths anyway. CHAR stores a fixed number of characters, but it might still be a variable length in bytes, because modern applications typically use UTF-8. The performance gain may only apply to fixed-width character sets, and only to MyISAM. Jan 3 at 4:10
  • @BillKarwin Is each element in the char(n) array equivalent to char or varchar?I guess varchar,because char use more space in spaces.But i haven't find any doc in here. Jan 3 at 6:57
  • @feilonggao Read dev.mysql.com/doc/refman/8.2/en/innodb-row-format.html and search for paragraphs that mention CHAR. Jan 3 at 7:07
  • I know char in there.What I am referring to here is char array index.Is each element stored in char format?why there is no varchar array index? Jan 4 at 5:51

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.