efficient use of varchar?

Does it make any difference if I allocate a particular number of bytes for a
varchar? I mean, just as an example, maybe its more efficient to use a power
of 2 as the field length. Or maybe power of 2 minus 1. I'm guessing not and
that anything less than 255 is the same.

I'm converting some data in a spreadsheet and I have to create a table with
about 150 columns. So I created all the columns varchar(255) and now I'm
asking mysql to show me the longest value in each column. "SELECT
MAX(LENGTH(column99)) FROM TABLE99". Then I've been modifying the "CREATE
TABLE" code to accomodate the longest value plus a little more. So if the
longest value in the column is 38 characters, I'd probably make that a
VARCHAR(50). But maybe I might as well make that 63 or 64. Or maybe I'm
wasting my time and should leave them all 255.



--
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: efficient use of varchar?

Hi,

There is difference between varchar(63) and varchar(38). Instead of
selecting MAX(LENGTH()) you can do PROCEDURE ANALYZE() on the table,
which will tell you about the optimal record type for the data you
currently have in the table.

When you are using inreasonably large varchar columns, and mysql decides
to create an in-memory temporary table with the MEMORY storage engine,
practically all varchar(n) fields will be converted to char(n) fields,
because memory storage engine doesn't support variable length data.

Peter Boros

On 04/06/2011 08:28 PM, John G. Heim wrote:
> Does it make any difference if I allocate a particular number of bytes
> for a varchar? I mean, just as an example, maybe its more efficient to
> use a power of 2 as the field length. Or maybe power of 2 minus 1. I'm
> guessing not and that anything less than 255 is the same.
>
> I'm converting some data in a spreadsheet and I have to create a table
> with about 150 columns. So I created all the columns varchar(255) and
> now I'm asking mysql to show me the longest value in each column.
> "SELECT MAX(LENGTH(column99)) FROM TABLE99". Then I've been modifying
> the "CREATE TABLE" code to accomodate the longest value plus a little
> more. So if the longest value in the column is 38 characters, I'd
> probably make that a VARCHAR(50). But maybe I might as well make that 63
> or 64. Or maybe I'm wasting my time and should leave them all 255.
>
>
>

--
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: efficient use of varchar?

>-----Original Message-----
>From: petya [mailto:petya@petya.org.hu]
>Sent: Wednesday, April 06, 2011 3:55 PM
>To: John G. Heim
>Cc: mysql@lists.mysql.com
>Subject: Re: efficient use of varchar?
>
>Hi,
>
>There is difference between varchar(63) and varchar(38). Instead of
>selecting MAX(LENGTH()) you can do PROCEDURE ANALYZE() on the table,
>which will tell you about the optimal record type for the data you
>currently have in the table.
>
[JS] Okay, it's my turn to be puzzled. I never used PROCEDURE ANALYSE (and you
**have** to use the UK English spelling, apparently), so I decided to try it.
I have a VARCHAR(255) field named `prod_title`. This is what came out:

==========
us-gii >SELECT prod_title FROM prod PROCEDURE ANALYSE()\G
*************************** 1. row ***************************
Field_name: giiexpr_db.prod.prod_title
Min_value:

High Throughput Screening 2003: Improving Strategies, Technologies, and
Productivity
Max_value:
?½ÿ?¡ë?¡ªµáí?fÑ?»å??ºµ¥â?«í?Éå??Ä?ê???ª????«??Åè?Çâµá??»ä??? ?«??èíµëï?åî
Min_length: 2
Max_length: 255
Empties_or_zeros: 0
Nulls: 0
Avg_value_or_avg_length: 54.0701
Std: NULL
Optimal_fieldtype: TINYTEXT NOT NULL
1 row in set (0.23 sec)

==========

Aside from Max_value, which I'll wonder about later, why is the
Optimal_fieldtype TINYTEXT?

Regards,

Jerry Schwartz
Global Information Incorporated
195 Farmington Ave.
Farmington, CT 06032

860.674.8796 / FAX: 860.674.8341
E-mail: jerry@gii.co.jp
Web site: www.the-infoshop.com




>When you are using inreasonably large varchar columns, and mysql decides
>to create an in-memory temporary table with the MEMORY storage engine,
>practically all varchar(n) fields will be converted to char(n) fields,
>because memory storage engine doesn't support variable length data.
>
>Peter Boros
>
>On 04/06/2011 08:28 PM, John G. Heim wrote:
>> Does it make any difference if I allocate a particular number of bytes
>> for a varchar? I mean, just as an example, maybe its more efficient to
>> use a power of 2 as the field length. Or maybe power of 2 minus 1. I'm
>> guessing not and that anything less than 255 is the same.
>>
>> I'm converting some data in a spreadsheet and I have to create a table
>> with about 150 columns. So I created all the columns varchar(255) and
>> now I'm asking mysql to show me the longest value in each column.
>> "SELECT MAX(LENGTH(column99)) FROM TABLE99". Then I've been modifying
>> the "CREATE TABLE" code to accomodate the longest value plus a little
>> more. So if the longest value in the column is 38 characters, I'd
>> probably make that a VARCHAR(50). But maybe I might as well make that 63
>> or 64. Or maybe I'm wasting my time and should leave them all 255.
>>
>>
>>
>
>--
>MySQL General Mailing List
>For list archives: http://lists.mysql.com/mysql
>To unsubscribe: http://lists.mysql.com/mysql?unsub=jerry@gii.co.jp





--
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: efficient use of varchar?

By the way, the weird-looking title is in Japanese (the database/table/field
are UTF-8).

????????????????????????

Some of you might be able to read that.

Is it possible that this is upsetting the ANALYSE procedure?

Regards,

Jerry Schwartz
Global Information Incorporated
195 Farmington Ave.
Farmington, CT 06032

860.674.8796 / FAX: 860.674.8341
E-mail: jerry@gii.co.jp
Web site: www.the-infoshop.com


>-----Original Message-----
>From: Jerry Schwartz [mailto:jerry@gii.co.jp]
>Sent: Thursday, April 07, 2011 12:09 PM
>To: 'petya'; 'John G. Heim'
>Cc: mysql@lists.mysql.com
>Subject: RE: efficient use of varchar?
>
>>-----Original Message-----
>>From: petya [mailto:petya@petya.org.hu]
>>Sent: Wednesday, April 06, 2011 3:55 PM
>>To: John G. Heim
>>Cc: mysql@lists.mysql.com
>>Subject: Re: efficient use of varchar?
>>
>>Hi,
>>
>>There is difference between varchar(63) and varchar(38). Instead of
>>selecting MAX(LENGTH()) you can do PROCEDURE ANALYZE() on the table,
>>which will tell you about the optimal record type for the data you
>>currently have in the table.
>>
>[JS] Okay, it's my turn to be puzzled. I never used PROCEDURE ANALYSE (and
>you
>**have** to use the UK English spelling, apparently), so I decided to try it.
>I have a VARCHAR(255) field named `prod_title`. This is what came out:
>
>==========
>us-gii >SELECT prod_title FROM prod PROCEDURE ANALYSE()\G
>*************************** 1. row ***************************
> Field_name: giiexpr_db.prod.prod_title
> Min_value:
>
>High Throughput Screening 2003: Improving Strategies, Technologies, and
>Productivity
> Max_value:
>?½ÿ?¡ë?¡ªµáí?fÑ?»å??ºµ¥â?«í?Éå??Ä?ê???ª????«??Åè?Çâµá??»ä?? ??«??èíµëï?åî
> Min_length: 2
> Max_length: 255
> Empties_or_zeros: 0
> Nulls: 0
>Avg_value_or_avg_length: 54.0701
> Std: NULL
> Optimal_fieldtype: TINYTEXT NOT NULL
>1 row in set (0.23 sec)
>
>==========
>
>Aside from Max_value, which I'll wonder about later, why is the
>Optimal_fieldtype TINYTEXT?
>
>Regards,
>
>Jerry Schwartz
>Global Information Incorporated
>195 Farmington Ave.
>Farmington, CT 06032
>
>860.674.8796 / FAX: 860.674.8341
>E-mail: jerry@gii.co.jp
>Web site: www.the-infoshop.com
>
>
>
>
>>When you are using inreasonably large varchar columns, and mysql decides
>>to create an in-memory temporary table with the MEMORY storage engine,
>>practically all varchar(n) fields will be converted to char(n) fields,
>>because memory storage engine doesn't support variable length data.
>>
>>Peter Boros
>>
>>On 04/06/2011 08:28 PM, John G. Heim wrote:
>>> Does it make any difference if I allocate a particular number of bytes
>>> for a varchar? I mean, just as an example, maybe its more efficient to
>>> use a power of 2 as the field length. Or maybe power of 2 minus 1. I'm
>>> guessing not and that anything less than 255 is the same.
>>>
>>> I'm converting some data in a spreadsheet and I have to create a table
>>> with about 150 columns. So I created all the columns varchar(255) and
>>> now I'm asking mysql to show me the longest value in each column.
>>> "SELECT MAX(LENGTH(column99)) FROM TABLE99". Then I've been modifying
>>> the "CREATE TABLE" code to accomodate the longest value plus a little
>>> more. So if the longest value in the column is 38 characters, I'd
>>> probably make that a VARCHAR(50). But maybe I might as well make that 63
>>> or 64. Or maybe I'm wasting my time and should leave them all 255.
>>>
>>>
>>>
>>
>>--
>>MySQL General Mailing List
>>For list archives: http://lists.mysql.com/mysql
>>To unsubscribe: http://lists.mysql.com/mysql?unsub=jerry@gii.co.jp
>
>
>
>
>
>--
>MySQL General Mailing List
>For list archives: http://lists.mysql.com/mysql
>To unsubscribe: http://lists.mysql.com/mysql?unsub=jerry@gii.co.jp





--
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