31

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?

Community
  • 1
  • 1
Jane Panda
  • 1,591
  • 4
  • 23
  • 51
  • 1
    There is a great comment in the above referenced article. "There's one possible performance impact: in MySQL, temporary tables and MEMORY tables store a VARCHAR column as a fixed-length column, padded out to its maximum length. If you design VARCHAR columns much larger than the greatest size you need, you will consume more memory than you have to. This affects cache efficiency, sorting speed, etc." – Riedsio Dec 02 '10 at 16:40

7 Answers7

19

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

ajreal
  • 46,720
  • 11
  • 89
  • 119
  • 1
    Keep in mind that temporary tables and MEMORY tables store a VARCHAR column as a fixed-length column, padded out to its maximum length. – kintsukuroi Sep 26 '19 at 01:21
15

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.

Community
  • 1
  • 1
symcbean
  • 47,736
  • 6
  • 59
  • 94
  • 2
    Keep in mind that temporary tables and MEMORY tables store a VARCHAR column as a fixed-length column, padded out to its maximum length. – kintsukuroi Sep 26 '19 at 01:21
  • Also MyISAM tables with fixed record sizes have significantly faster lookup times. – symcbean Jul 30 '20 at 21:13
5

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.

Pentium10
  • 204,586
  • 122
  • 423
  • 502
  • 1
    Keep in mind that temporary tables and MEMORY tables store a VARCHAR column as a fixed-length column, padded out to its maximum length. – kintsukuroi Sep 26 '19 at 01:22
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 :)

Eleazan
  • 438
  • 2
  • 6
  • 2
    Keep in mind that temporary tables and MEMORY tables store a VARCHAR column as a fixed-length column, padded out to its maximum length. – kintsukuroi Sep 26 '19 at 01:21
2

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

Spencer Ruport
  • 34,865
  • 12
  • 85
  • 147
  • 3
    It can have a massive effect on performance. If you use even one VarChar column (or Text, or other variable width column), MySQL uses a dynamic width row format. So to do a table scan will require it to read every row sequentially to find the next. The difference between skipping over a few hundred bytes and a few thousand bytes (because of multiple overlong VarChars) can be very significant (mostly depending upon the underlying drive structure and block devices). But in short, it can absolutely hut performance (mostly for large tables with GB of data will you notice it however)... – ircmaxell Dec 01 '10 at 13:54
0

If you set it to 20, it will save only the first 20 characters. So yes, it will take up less space than 255 characters :).

Yeroon
  • 3,223
  • 2
  • 22
  • 29
0

The required storage space for VARCHAR is as follows:

VARCHAR(L), VARBINARY(L) — L + 1 bytes if column values require 0 – 255 bytes, L + 2 bytes if values may require more than 255 bytes

So VARCHAR does only require the space for the string plus one or two additional bytes for the length of the string.

Gumbo
  • 643,351
  • 109
  • 780
  • 844