storage difference in VARCHAR(size)?

--00c09fa21b3ac93c2f04780cc99c
Content-Type: text/plain; charset=ISO-8859-1

Hey all,

I was building a table for storing email addresses today and ran into an
issue that I couldn't find an answer for using Google. If I declare the
column as a VARCHAR (this is an InnoDB table), does it matter what size I
declare it as if it's between 1 and 255? I know there's an extra byte of
storage once it goes above 255 because of the length, but is there a storage
difference between VARCHAR(100) and VARCHAR(255)? Obviously there's a
functional difference in what gets cut off when I try to store into that
table, but is that the only difference?

Thanks for any insight,
Waynn

--00c09fa21b3ac93c2f04780cc99c--

Re: storage difference in VARCHAR(size)?

You have stumbled across the secret. No, there is no difference at
all as the calculations suggested here confirm.

http://dev.mysql.com/doc/refman/5.1/en/storage-requirements. html

On Tue, Nov 10, 2009 at 6:37 PM, Waynn Lue wrote:
> Hey all,
>
> I was building a table for storing email addresses today and ran into an
> issue that I couldn't find an answer for using Google. =A0If I declare th=
e
> column as a VARCHAR (this is an InnoDB table), does it matter what size I
> declare it as if it's between 1 and 255? =A0I know there's an extra byte =
of
> storage once it goes above 255 because of the length, but is there a stor=
age
> difference between VARCHAR(100) and VARCHAR(255)? =A0Obviously there's a
> functional difference in what gets cut off when I try to store into that
> table, but is that the only difference?
>
> Thanks for any insight,
> Waynn
>



--=20
- michael dykman
- mdykman@gmail.com

"May you live every day of your life."
Jonathan Swift

Larry's First Law of Language Redesign: Everyone wants the colon.

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg

Re: storage difference in VARCHAR(size)?

>You have stumbled across the secret. No, there is no difference at
>all as the calculations suggested here confirm.
>
>http://dev.mysql.com/doc/refman/5.1/en/storage-requirements .html

Note: as you can see in the above, CHAR data DOES take up room for it's
full size, stupidly enough.

On Tue, Nov 10, 2009 at 6:37 PM, Waynn Lue wrote:
> Hey all,
>
> I was building a table for storing email addresses today and ran into an
> issue that I couldn't find an answer for using Google. If I declare the
> column as a VARCHAR (this is an InnoDB table), does it matter what size I
> declare it as if it's between 1 and 255? I know there's an extra byte of
> storage once it goes above 255 because of the length, but is there a
> storage
> difference between VARCHAR(100) and VARCHAR(255)? Obviously there's a
> functional difference in what gets cut off when I try to store into that
> table, but is that the only difference?

With regards,

Martijn Tonies
Upscene Productions
http://www.upscene.com

Download Database Workbench for Oracle, MS SQL Server, Sybase SQL
Anywhere, MySQL, InterBase, NexusDB and Firebird!

Database questions? Check the forum:
http://www.databasedevelopmentforum.com


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: storage difference in VARCHAR(size)?

Your mail suggests that you *are* seeing a difference, though. What
are you seeing?

On 11/11/09, Martijn Tonies wrote:
>>You have stumbled across the secret. No, there is no difference at
>>all as the calculations suggested here confirm.
>>
>>http://dev.mysql.com/doc/refman/5.1/en/storage-requirement s.html
>
> Note: as you can see in the above, CHAR data DOES take up room for it's
> full size, stupidly enough.
>
> On Tue, Nov 10, 2009 at 6:37 PM, Waynn Lue wrote:
>> Hey all,
>>
>> I was building a table for storing email addresses today and ran into an
>> issue that I couldn't find an answer for using Google. If I declare the
>> column as a VARCHAR (this is an InnoDB table), does it matter what size I
>> declare it as if it's between 1 and 255? I know there's an extra byte of
>> storage once it goes above 255 because of the length, but is there a
>> storage
>> difference between VARCHAR(100) and VARCHAR(255)? Obviously there's a
>> functional difference in what gets cut off when I try to store into that
>> table, but is that the only difference?
>
> With regards,
>
> Martijn Tonies
> Upscene Productions
> http://www.upscene.com
>
> Download Database Workbench for Oracle, MS SQL Server, Sybase SQL
> Anywhere, MySQL, InterBase, NexusDB and Firebird!
>
> Database questions? Check the forum:
> http://www.databasedevelopmentforum.com
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql?unsub=vegivamp@tuxera.be
>
>

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: storage difference in VARCHAR(size)?

--00c09f972608ee84be04781d60cc
Content-Type: text/plain; charset=ISO-8859-1

Oh, I wasn't seeing a difference, I just wanted to make sure I understood
how the two choices affected our system.

Thanks so much for all the answers!

On Wed, Nov 11, 2009 at 11:12 AM, Johan De Meersman wrote:

> Your mail suggests that you *are* seeing a difference, though. What
> are you seeing?
>
> On 11/11/09, Martijn Tonies wrote:
> >>You have stumbled across the secret. No, there is no difference at
> >>all as the calculations suggested here confirm.
> >>
> >>http://dev.mysql.com/doc/refman/5.1/en/storage-requirement s.html
> >
> > Note: as you can see in the above, CHAR data DOES take up room for it's
> > full size, stupidly enough.
> >
> > On Tue, Nov 10, 2009 at 6:37 PM, Waynn Lue wrote:
> >> Hey all,
> >>
> >> I was building a table for storing email addresses today and ran into an
> >> issue that I couldn't find an answer for using Google. If I declare the
> >> column as a VARCHAR (this is an InnoDB table), does it matter what size
> I
> >> declare it as if it's between 1 and 255? I know there's an extra byte of
> >> storage once it goes above 255 because of the length, but is there a
> >> storage
> >> difference between VARCHAR(100) and VARCHAR(255)? Obviously there's a
> >> functional difference in what gets cut off when I try to store into that
> >> table, but is that the only difference?
> >
> > With regards,
> >
> > Martijn Tonies
> > Upscene Productions
> > http://www.upscene.com
> >
> > Download Database Workbench for Oracle, MS SQL Server, Sybase SQL
> > Anywhere, MySQL, InterBase, NexusDB and Firebird!
> >
> > Database questions? Check the forum:
> > http://www.databasedevelopmentforum.com
> >
> >
> > --
> > MySQL General Mailing List
> > For list archives: http://lists.mysql.com/mysql
> > To unsubscribe: http://lists.mysql.com/mysql?unsub=vegivamp@tuxera.be
> >
> >
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql?unsub=waynnlue@gmail.com
>
>

--00c09f972608ee84be04781d60cc--

Re: storage difference in VARCHAR(size)?

> Your mail suggests that you *are* seeing a difference, though. What
> are you seeing?

What I was saying, is that VARCHAR takes up space "l" (= length)
of the data plus 1 or 2 bytes to store the length, while CHAR takes
up the full space of the -defined- column size.

This is rather wasteful when storing CHAR data that doesn't take up
the full available space (as this is padded with spaces up to the defined
length) and is a storage engine "feature" ;-)

With regards,

Martijn Tonies
Upscene Productions
http://www.upscene.com

Download Database Workbench for Oracle, MS SQL Server, Sybase SQL
Anywhere, MySQL, InterBase, NexusDB and Firebird!

Database questions? Check the forum:
http://www.databasedevelopmentforum.com


> On 11/11/09, Martijn Tonies wrote:
>>>You have stumbled across the secret. No, there is no difference at
>>>all as the calculations suggested here confirm.
>>>
>>>http://dev.mysql.com/doc/refman/5.1/en/storage-requiremen ts.html
>>
>> Note: as you can see in the above, CHAR data DOES take up room for it's
>> full size, stupidly enough.
>>
>> On Tue, Nov 10, 2009 at 6:37 PM, Waynn Lue wrote:
>>> Hey all,
>>>
>>> I was building a table for storing email addresses today and ran into an
>>> issue that I couldn't find an answer for using Google. If I declare the
>>> column as a VARCHAR (this is an InnoDB table), does it matter what size
>>> I
>>> declare it as if it's between 1 and 255? I know there's an extra byte of
>>> storage once it goes above 255 because of the length, but is there a
>>> storage
>>> difference between VARCHAR(100) and VARCHAR(255)? Obviously there's a
>>> functional difference in what gets cut off when I try to store into that
>>> table, but is that the only difference?
>>
>> With regards,
>>
>> Martijn Tonies
>> Upscene Productions
>> http://www.upscene.com
>>
>> Download Database Workbench for Oracle, MS SQL Server, Sybase SQL
>> Anywhere, MySQL, InterBase, NexusDB and Firebird!
>>
>> Database questions? Check the forum:
>> http://www.databasedevelopmentforum.com
>>
>>
>> --
>> MySQL General Mailing List
>> For list archives: http://lists.mysql.com/mysql
>> To unsubscribe: http://lists.mysql.com/mysql?unsub=vegivamp@tuxera.be
>>
>>


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: storage difference in VARCHAR(size)?

Aware of that, but char brings you certain performance benefits that
are good to know about if you need every last drop of juice :-)

On 11/11/09, Martijn Tonies wrote:
>
>
>> Your mail suggests that you *are* seeing a difference, though. What
>> are you seeing?
>
> What I was saying, is that VARCHAR takes up space "l" (= length)
> of the data plus 1 or 2 bytes to store the length, while CHAR takes
> up the full space of the -defined- column size.
>
> This is rather wasteful when storing CHAR data that doesn't take up
> the full available space (as this is padded with spaces up to the defined
> length) and is a storage engine "feature" ;-)
>
> With regards,
>
> Martijn Tonies
> Upscene Productions
> http://www.upscene.com
>
> Download Database Workbench for Oracle, MS SQL Server, Sybase SQL
> Anywhere, MySQL, InterBase, NexusDB and Firebird!
>
> Database questions? Check the forum:
> http://www.databasedevelopmentforum.com
>
>
>> On 11/11/09, Martijn Tonies wrote:
>>>>You have stumbled across the secret. No, there is no difference at
>>>>all as the calculations suggested here confirm.
>>>>
>>>>http://dev.mysql.com/doc/refman/5.1/en/storage-requireme nts.html
>>>
>>> Note: as you can see in the above, CHAR data DOES take up room for it's
>>> full size, stupidly enough.
>>>
>>> On Tue, Nov 10, 2009 at 6:37 PM, Waynn Lue wrote:
>>>> Hey all,
>>>>
>>>> I was building a table for storing email addresses today and ran into an
>>>> issue that I couldn't find an answer for using Google. If I declare the
>>>> column as a VARCHAR (this is an InnoDB table), does it matter what size
>>>> I
>>>> declare it as if it's between 1 and 255? I know there's an extra byte of
>>>> storage once it goes above 255 because of the length, but is there a
>>>> storage
>>>> difference between VARCHAR(100) and VARCHAR(255)? Obviously there's a
>>>> functional difference in what gets cut off when I try to store into that
>>>> table, but is that the only difference?
>>>
>>> With regards,
>>>
>>> Martijn Tonies
>>> Upscene Productions
>>> http://www.upscene.com
>>>
>>> Download Database Workbench for Oracle, MS SQL Server, Sybase SQL
>>> Anywhere, MySQL, InterBase, NexusDB and Firebird!
>>>
>>> Database questions? Check the forum:
>>> http://www.databasedevelopmentforum.com
>>>
>>>
>>> --
>>> MySQL General Mailing List
>>> For list archives: http://lists.mysql.com/mysql
>>> To unsubscribe: http://lists.mysql.com/mysql?unsub=vegivamp@tuxera.be
>>>
>>>
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql?unsub=vegivamp@tuxera.be
>
>

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org