Does VARCHAR size limit matter?

Question

Possible Duplicate:
Importance of varchar length in MySQL table

When using VARCHAR (assuming this is the correct data type for a short string) does the size matter? If I set it to 20 characters, will that take up less space or be faster than 255 characters?

Solution 1

In general, for a VARCHAR field, the amount of data stored in each field determines its footprint on the disk rather than the maximum size (unlike a CHAR field which always has the same footprint).

There is an upper limit on the total data stored within all fields of an index of 900 bytes (900 byte index size limit in character length).

The larger you make the field, the more likely people will try to use for purposes other than what you intended - and the greater the screen real-estate required to show the value - so its good practice to try to pick the right size, rather than assuming that if you make it as large as possible it will save you having to revisit the design.

Solution 2

Yes, is matter when you indexing multiple columns.

Prefixes can be up to 1000 bytes long (767 bytes for InnoDB tables). Note that prefix limits are measured in bytes, whereas the prefix length in CREATE TABLE statements is interpreted as number of characters. Be sure to take this into account when specifying a prefix length for a column that uses a multi-byte character set.

source : http://dev.mysql.com/doc/refman/5.0/en/column-indexes.html

In a latin1 collation, you can only specify up 3 columns of varchar(255).
While can specify up to 50 columns for varchar(20)

In-directly, without proper index, it will slow-down query speed

In terms of storage, it does not make difference,
as varchar stand for variable-length strings

Solution 3

The actual differences are:

• TINYTEXT and other TEXT fields are stored separately from in-memory row inside MySQL heap, whereas VARCHAR() fields add up to 64k limit (so you can have more than 64k in TINYTEXTs, whereas you won't with VARCHAR).

• TINYTEXT and other 'blob-like' fields will force SQL layer (MySQL) to use on-disk temporary tables whenever they are used, whereas VARCHAR will be still sorted 'in memory' (though will be converted to CHAR for the full width).

• InnoDB internally doesn't really care whether it is tinytext or varchar. It is very easy to verify, create two tables, one with VARCHAR(255), another with TINYINT, and insert a record to both. They both will take single 16k page - whereas if overflow pages are used, TINYTEXT table should show up as taking at least 32k in 'SHOW TABLE STATUS'.

I usually prefer VARCHAR(255) - they don't cause too much of heap fragmentation for single row, and can be treated as single 64k object in memory inside MySQL. On InnoDB size differences are negligible.

Solution 4

In the documentation of MySQL: http://dev.mysql.com/doc/refman/5.0/en/char.html

You have a table that indicates the bytes of a VARCHAR(4) (vs a CHAR(4)).

A simple VARCHAR(4) without string, only 1 byte. Then, a simple VARCHAR(255) without string is 1byte. A VARCHAR(4) with 'ab' is 3 bytes, and a VARCHAR(255) with 'ab' is 3 bytes. It's the same, but with the lenght limit :)

Solution 5

This will have no effect on performance. In this case the constraint merely helps ensure data integrity.

View more solutions