Performance implications of using type varchar (20) and varchar (255) in MySQL

1.when MySQL indexes are indexed without limiting the size of the index, theindex length defaults to the length of the field, meaning that the index lengths for varchar (+) and varchar (255) are 20*3 ( Utf-8) (+2+1), 255*3 (Utf-8) (+2+1), where "+2" is used to store the length information, "+1" is used to mark whether it is empty, and the varchar (255) type is used to load more memory when loading the index information; Note: If the field is defined as non-empty, the mark will not take up bytes if it is empty) For example, the test SQL (InnoDB engine) is as follows:

CREATE DATABASE testdatabaseuse testdatabasecreate TABLE ABC (  ' id ' int (one) DEFAULT NULL,  ' name ' varchar) DEFA ULT NULL) engine=innodb DEFAULT charset=utf8alter  TABLE  ' ABC '  ADD  INDEX ' nameindex ' (' name ') explain Select name from abcalter table ABC  modify name varchar (255) Explain select name from ABC

the Ken_len in the result indicates the number of bytes used by the index:the formula for calculating the length of Key_len:

VARCHR (10) Variable length field and allow null:10* (Character set:utf8=3,gbk=2,latin1=1) +1 (NULL) +2 (variable-length field) VARCHR (10) Variable-length field and do not allow null:10* ( Character set:utf8=3,gbk=2,latin1=1) +2 (variable-length field) char (10) Fixed field and allow null:10* (Character set:utf8=3,gbk=2,latin1=1) +1 (NULL) char (10) Fixed field and allow null:10* (Character set:utf8=3,gbk=2,latin1=1)

based on this value, it is possible to determine index usage, especially when combining indexes, to determine that all indexed fields are queried.
2. varchar (20) and varchar (255) are all strings that remain mutable, and when you create a MyISAM table using row_format=fixed, a fixed length space is used for each row, so that when different varchar length values are set, The space occupied by storing the same data is different. in general, using varchar (20) and varchar (255) to keep the ' hello ' occupied space is the same, but using a shorter column has a huge advantage. Larger columns use more memory because MySQL typically allocates a fixed-size block of memory to hold values, which is especially bad for sorting or using memory-based temporal tables. The same thing can happen when you use file sorting or disk-based temporal tables.

Performance implications of using type varchar (20) and varchar (255) in MySQL