Hi,

I have a basic question.
If i declare a column as VARCHAR(20) even though i am sure that it is
not going to contain values of length greater than 15, Will this
affect the performance of mysql query retrieval???
If so how?
Can any one please explain.

Thanks,
Sarath

On 15 May, 07:29, bssar...@gmail.com wrote:
> Hi,
>
> I have a basic question.
> If i declare a column as VARCHAR(20) even though i am sure that it is
> not going to contain values of length greater than 15, Will this
> affect the performance of mysql query retrieval???
> If so how?
> Can any one please explain.
>
> Thanks,
> Sarath

http://dev.mysql.com/doc/refman/5.0/en/char.html
My understanding is that the data is truncated so it will be of no
consequence.

On 15 May, 14:27, "petethebl...@googlemail.com"
<petethebl...@googlemail.com> wrote:
> On 15 May, 07:29, bssar...@gmail.com wrote:
>
> > Hi,
>
> > I have a basic question.
> > If i declare a column as VARCHAR(20) even though i am sure that it is
> > not going to contain values of length greater than 15, Will this
> > affect the performance of mysql query retrieval???
> > If so how?
> > Can any one please explain.
>
> > Thanks,
> > Sarath
>
> http://dev.mysql.com/doc/refman/5.0/en/char.html
> My understanding is that the data is truncated so it will be of no
> consequence.

Sorry. Phrased that wrongly.
The storage space is shortened. The data is not truncated. Read the
page I pointed to.

bssarath@gmail.com wrote:
> Hi,
>
> I have a basic question.
> If i declare a column as VARCHAR(20) even though i am sure that it is
> not going to contain values of length greater than 15, Will this
> affect the performance of mysql query retrieval???
> If so how?
> Can any one please explain.
>
> Thanks,
> Sarath
>

No.

Sarath,

> If i declare a column as VARCHAR(20) even though i am sure that it is
> not going to contain values of length greater than 15, Will this
> affect the performance of mysql query retrieval???
> If so how?
> Can any one please explain.

When you do a search, MySQL has to find its way to the data
in every row. If your data rows are fixed in length (no use of
varchar or other variable length fields) the search "can" be faster
and indexing can be faster.

http://dev.mysql.com/doc/refman/5.0/en/data-size.html

--
John
__________________________________________________ _________________
John Murtari Software Workshop Inc.
jmurtari@following domain 315.635-1968(x-211) "TheBook.Com" (TM)
http://thebook.com/

John Murtari wrote:
> Sarath,
>
>> If i declare a column as VARCHAR(20) even though i am sure that it is
>> not going to contain values of length greater than 15, Will this
>> affect the performance of mysql query retrieval???
>> If so how?
>> Can any one please explain.
>
> When you do a search, MySQL has to find its way to the data
> in every row. If your data rows are fixed in length (no use of
> varchar or other variable length fields) the search "can" be faster
> and indexing can be faster.
>
> http://dev.mysql.com/doc/refman/5.0/en/data-size.html
>

Then, what you are saying is that he should use all CHAR() definitions.

Unless they have finally fixed this, the retrieval of data may have a
higher CPU cost, because of the way the add/strip trailing spaces,
making an ANSI style CHAR() retrieval impossible.

In the **ANSI standard data-types**, if you store 15 bytes of data in a
VARCHAR(20) field, you expect to have 15 characters returned. If you
store 15 bytes of data in a CHAR(20) field - you expect to get your 15
bytes + 5 bytes space-filled data. Geniuses at MYSQL made it so it
would only return the 15 CHAR bytes you entered. Obviously they have
never had to deal with COBOL programming - that is still alive and well
in most major and a lot of minor shops around the world.

At this point let's just make up our own definitions for terms like
data-type and what is and what does. But because this behavior is
"documented", it makes it somehow okay.

On May 18, 1:28 am, Michael Austin <maus...@firstdbasource.com> wrote:
> John Murtari wrote:
> >Sarath,
>
> >> If i declare a column as VARCHAR(20) even though i am sure that it is
> >> not going to contain values of length greater than 15, Will this
> >> affect the performance of mysql query retrieval???
> >> If so how?
> >> Can any one please explain.
>
> > When you do a search, MySQL has to find its way to the data
> > in every row. If your data rows are fixed in length (no use of
> > varchar or other variable length fields) the search "can" be faster
> > and indexing can be faster.
>
> >http://dev.mysql.com/doc/refman/5.0/en/data-size.html
>
> Then, what you are saying is that he should use all CHAR() definitions.
>
> Unless they have finally fixed this, the retrieval of data may have a
> higher CPU cost, because of the way the add/strip trailing spaces,
> making an ANSI style CHAR() retrieval impossible.
>
> In the **ANSI standard data-types**, if you store 15 bytes of data in a
> VARCHAR(20) field, you expect to have 15 characters returned. If you
> store 15 bytes of data in a CHAR(20) field - you expect to get your 15
> bytes + 5 bytes space-filled data. Geniuses at MYSQL made it so it
> would only return the 15 CHAR bytes you entered. Obviously they have
> never had to deal with COBOL programming - that is still alive and well
> in most major and a lot of minor shops around the world.
>
> At this point let's just make up our own definitions for terms like
> data-type and what is and what does. But because this behavior is
> "documented", it makes it somehow okay.

Thank you guys, i got the answer.

Sarath