MySQL uses the type varchar (20) and varchar (255) to affect the performance, mysqlvarchar

MySQL uses the type varchar (20) and varchar (255) to affect the performance, mysqlvarchar

1.If the index size is not limited when MySQL creates an index, the index length will use the length of this field by default, that is, varchar (20) and varchar (255) the corresponding index lengths are 20*3 (UTF-8) (+ 2 + 1), 255*3 (UTF-8) (+ 2 + 1 ), "+ 2" is used to store length information, and "+ 1" is used to mark whether it is null. When loading index information, the varchar (255) type will occupy more memory. (Note: when a field is defined as non-empty, the null flag does not occupy bytes. For example, test SQL (InnoDB engine) as follows:

CREATE DATABASE TestDataBaseUSE TestDataBaseCREATE TABLE ABC (  `id` int(11) DEFAULT NULL,  `name` varchar(20) DEFAULT 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

In the result, ken_len indicates the number of bytes used by the index: 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 NULL: 10 * (Character Set: utf8 = 3, gbk = 2, latin1 = 1) + 2 (variable length field) char (10) fixed field and allowed NULL: 10 * (Character Set: utf8 = 3, gbk = 2, latin1 = 1) + 1 (NULL) char (10) fixed field and allowed NULL: 10 * (Character Set: utf8 = 3, gbk = 2, latin1 = 1)

Based on this value, you can determine the index usage, especially when combining indexes, to determine that all index fields are used for query.
2. Both varchar (20) and varchar (255) are variable strings. When a MyISAM table is created using ROW_FORMAT = FIXED, a FIXED length space is used for each row, in this way, when different varchar length values are set, the space used to store the same data is different. Generally, the use of varchar (20) and varchar (255) ensures that the space occupied by 'Hello' is the same, but the use of Short Columns has a huge advantage. Large columns use more memory, because MySQL usually allocates a fixed size of memory blocks to save values, which is especially bad for sorting or using memory-based temporary tables. The same thing will happen when you use File Sorting or disk-based temporary tables.