How Much Real Storage Is Used with a Varchar(100) Declaration in MySQL

How much real storage is used with a varchar(100) declaration in mysql?

If I have a table with a field which
is declared as accepting varchar(100)
and then I actually insert the word
"hello" how much real storage will be
used on the mysql server?

Mysql will store 5 bytes plus one byte for the length. If the varchar is greater than 255, then it will store 2 bytes for the length.

Note that this is dependent on the charset of the column. If the charset is utf8, mysql will require up to 3 bytes per character. Some storage engines (i.e. memory) will always require the maximum byte length per character for the character set.

Also will an insert of NULL result in
no storage being used even though
varchar(100) is declared?

Making a column nullable means that mysql will have to set aside an extra byte per up to 8 nullable columns per row. This is called the "null mask".

What ever the answer is, is it consistent accross different database implementations?

It's not even consistent between storage engines within mysql!

MySQL VARCHAR size?

100 characters.

This is the var (variable) in varchar: you only store what you enter (and an extra 2 bytes to store length upto 65535)

If it was char(200) then you'd always store 200 characters, padded with 100 spaces

See the docs: "The CHAR and VARCHAR Types"

MySQL: varchar(64) vs. varchar(255), is there a difference for small strings?

No. Some people just use VARCHAR(255) for all short strings columns. For CHAR columns, all strings will padded with blanks up to the defined length.

Best practices for SQL varchar column length

No DBMS I know of has any "optimization" that will make a VARCHAR with a 2^n length perform better than one with a max length that is not a power of 2.

I think early SQL Server versions actually treated a VARCHAR with length 255 differently than one with a higher maximum length. I don't know if this is still the case.

For almost all DBMS, the actual storage that is required is only determined by the number of characters you put into it, not the max length you define. So from a storage point of view (and most probably a performance one as well), it does not make any difference whether you declare a column as VARCHAR(100) or VARCHAR(500).

You should see the max length provided for a VARCHAR column as a kind of constraint (or business rule) rather than a technical/physical thing.

For PostgreSQL the best setup is to use text without a length restriction and a CHECK CONSTRAINT that limits the number of characters to whatever your business requires.

If that requirement changes, altering the check constraint is much faster than altering the table (because the table does not need to be re-written)

The same can be applied for Oracle and others - in Oracle it would be VARCHAR(4000) instead of text though.

I don't know if there is a physical storage difference between VARCHAR(max) and e.g. VARCHAR(500) in SQL Server. But apparently there is a performance impact when using varchar(max) as compared to varchar(8000).

See this link (posted by Erwin Brandstetter as a comment)

Edit 2013-09-22

Regarding bigown's comment:

In Postgres versions before 9.2 (which was not available when I wrote the initial answer) a change to the column definition did rewrite the whole table, see e.g. here. Since 9.2 this is no longer the case and a quick test confirmed that increasing the column size for a table with 1.2 million rows indeed only took 0.5 seconds.

For Oracle this seems to be true as well, judging by the time it takes to alter a big table's varchar column. But I could not find any reference for that.

For MySQL the manual says "In most cases, ALTER TABLE makes a temporary copy of the original table". And my own tests confirm that: running an ALTER TABLE on a table with 1.2 million rows (the same as in my test with Postgres) to increase the size of a column took 1.5 minutes. In MySQL however you can not use the "workaround" to use a check constraint to limit the number of characters in a column.

For SQL Server I could not find a clear statement on this but the execution time to increase the size of a varchar column (again the 1.2 million rows table from above) indicates that no rewrite takes place.

Edit 2017-01-24

Seems I was (at least partially) wrong about SQL Server. See this answer from Aaron Bertrand that shows that the declared length of a nvarchar or varchar columns makes a huge difference for the performance.

Why should I ever choose any other length than 255 for varchar in MySQL?

1) If you dont want to limit the maximum size of a stored varchar, then yes it is fine. That being said...

2) In many cases you want to set an upper limit for the size of a varchar. Lets say you are storing a mailing list, and have a limited amount of space for an address line. By setting an upper limit for your address field, you now allow the database to enforce a maximum address line length for you.

Importance of varchar length in MySQL table

No, in the sense that if the values you're storing in that column are always (say) less than 50 characters, declaring the column as varchar(50) or varchar(200) has the same performance.

Database Memory :: MySql Innodb how much storage memory allocation for empty field vs not empty field - varchar or text

do you mean RAM or disk space?

for disk space, the varchar will not use up space unless there are values in the string.

Does VARCHAR size limit matter?

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.

Mysql memory efficiency and type declaration

1. No, VARCHAR is meant for variable length text, while CHAR is fixed length. The number parameter is the character limit for the text but VARCHAR only uses up as much space as the actual characters you enter in that row (+ some bytes to store the length used).

2. MySQL, Microsoft SQL Server and pretty much all relational databases work the same way with VARCHAR. Every column takes up some minimum amount of space in a row but with VARCHAR it would be the bytes to store the text + bytes to store the length of the text. No text entered would mean just a 1 or 2 bytes used to save '0' as the length.

3. If you don't know how much text data will be entered, then use LONGTEXT in MySQL or NVARCHAR(MAX) in MS-SQL. This datatype allows you to store an unlimited amount of text efficiently (up to the row size limitations of the database itself). It's just a bigger, unlimited length version of standard VARCAHR.

What is the real point of adding sizes to data types when declaring a table in MySQL?

MySQL has always been a more casual database than others that take things like constraints very seriously. It steers towards the "do what I mean, not what I say" interpretation of things more often than not.

For integer numerical types the specifier is only there for padding purposes, not as a limit. For character types that's the maximum length that can be stored in that field without forced truncation. This truncation is done silently by default, you'll never know it happens, which can be trouble.

For non-integer fields it's always recommended to specify the length you need explicitly. This helps others understand your intent. For example, specifying VARCHAR(100) for a name means that the field can only contain 100 characters, which by extension means any forms that interface with that field should verify the content will fit.

Applying this advice yields the following schema:

CREATE TABLE user (
    id INT UNSIGNED NOT NULL PRIMARY KEY,
    email VARCHAR(255) NOT NULL,
    password VARCHAR(255) NOT NULL,
    is_admin TINYINT UNSIGNED NOT NULL DEFAULT 0
);

---

Related Topics