Re: Effect of VARCHAR length?
Hello. Really, a varchar(255) column gives you a big flexibility and may save a lot of space. Usually such records uses as many characters as needed plus one byte for it's length. When you use MyISAM tables you can get some performance disadvantages and use more space when your table is fragmented (you can solve this using OPTIMIZE TABLE). But for InnoDB tables it is recommended to use varchar columns, however this storage engine is not so fast as MyISAM. If you need a column for which trailing spaces are not removed, consider using a BLOB or TEXT type. Yves Goergen [EMAIL PROTECTED] wrote: Hi list, I've just been wondering if the length parameter of a VARCHAR column has any effect on storage efficiency or space requirements. Afaik, VARCHAR columns only store the amount of data actually written into them and require no significantly more memory. So to be especially flexible with a particular table column, could I just define it VARCHAR(255) and face no further disadvantage of it? Thanks for the info... -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Effect of VARCHAR length?
I've just been wondering if the length parameter of a VARCHAR column has
any effect on storage efficiency or space requirements. Afaik, VARCHAR
columns only store the amount of data actually written into them and
require no significantly more memory. So to be especially flexible with
a particular table column, could I just define it VARCHAR(255) and face
no further disadvantage of it?
mysql CREATE TABLE vc (
- vc1 VARCHAR(5),
- vc2 VARCHAR(255)
- );
Query OK, 0 rows affected (0.16 sec)
mysql INSERT INTO vc (vc1, vc2) VALUES
- ('this is a test', 'this is another, longer test');
Query OK, 1 row affected, 1 warning (0.06 sec)
mysql SELECT * FROM vc;
+--+--+
| vc1 | vc2 |
+--+--+
| this | this is another, longer test |
+--+--+
1 row in set (0.00 sec)
From this, we can see how defining the field as VARCHAR(5) limits the
maximum length to 5 characters; we can assume, too, that it will
likewise chop off any strings longer than 255 characters in vc2 the
same way. The length parameter simply provides the upper limit of the
string that might be stored in that field, useful in some instances,
irrelevant in others.
All VARCHARs/TINYTEXTs are stored with a single-byte length prefix,
regardless of how long you let them be (less than 256, of course),
plus the string it's storing. So, for maximum flexibility less than
256 characters, use VARCHAR(255) and don't worry about it.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Effect of VARCHAR length?
Mike Rains ha scritto:
I've just been wondering if the length parameter of a VARCHAR column has
any effect on storage efficiency or space requirements. Afaik, VARCHAR
columns only store the amount of data actually written into them and
require no significantly more memory. So to be especially flexible with
a particular table column, could I just define it VARCHAR(255) and face
no further disadvantage of it?
mysql CREATE TABLE vc (
- vc1 VARCHAR(5),
- vc2 VARCHAR(255)
- );
Query OK, 0 rows affected (0.16 sec)
mysql INSERT INTO vc (vc1, vc2) VALUES
- ('this is a test', 'this is another, longer test');
Query OK, 1 row affected, 1 warning (0.06 sec)
mysql SELECT * FROM vc;
+--+--+
| vc1 | vc2 |
+--+--+
| this | this is another, longer test |
+--+--+
1 row in set (0.00 sec)
From this, we can see how defining the field as VARCHAR(5) limits the
maximum length to 5 characters; we can assume, too, that it will
likewise chop off any strings longer than 255 characters in vc2 the
same way. The length parameter simply provides the upper limit of the
string that might be stored in that field, useful in some instances,
irrelevant in others.
All VARCHARs/TINYTEXTs are stored with a single-byte length prefix,
regardless of how long you let them be (less than 256, of course),
plus the string it's storing. So, for maximum flexibility less than
256 characters, use VARCHAR(255) and don't worry about it.
I'm just curious to know if the length of the indexes on a varchar
column work in the same way or if they have a fixed lenght.
anybody knows ?
--
No problem is so formidable that you can't walk away from it.
~ Charles M. Schulz
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Effect of VARCHAR length?
I'm just curious to know if the length of the indexes on a varchar column work in the same way or if they have a fixed lenght. anybody knows ? I don't see how they could be fixed length, since VARCHAR itself is not fixed-length. Ergo, it makes sense that the prefix limitation is the upper prefix length limit. Reference: http://dev.mysql.com/doc/mysql/en/mysql-indexes.html http://dev.mysql.com/doc/mysql/en/create-index.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
