List:General Discussion« Previous MessageNext Message »
From:Jerry Schwartz Date:April 7 2011 4:14pm
Subject:RE: efficient use of varchar?
View as plain text  
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?


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

860.674.8796 / FAX: 860.674.8341
E-mail: jerry@stripped
Web site:

>-----Original Message-----
>From: Jerry Schwartz [mailto:jerry@stripped]
>Sent: Thursday, April 07, 2011 12:09 PM
>To: 'petya'; 'John G. Heim'
>Cc: mysql@stripped
>Subject: RE: efficient use of varchar?
>>-----Original Message-----
>>From: petya [mailto:petya@stripped]
>>Sent: Wednesday, April 06, 2011 3:55 PM
>>To: John G. Heim
>>Cc: mysql@stripped
>>Subject: Re: efficient use of varchar?
>>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 
>**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:
>              Min_value:
>High Throughput Screening 2003: Improving Strategies, Technologies, and
>              Max_value:
>             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?
>Jerry Schwartz
>Global Information Incorporated
>195 Farmington Ave.
>Farmington, CT 06032
>860.674.8796 / FAX: 860.674.8341
>E-mail: jerry@stripped
>Web site:
>>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:
>>To unsubscribe:
>MySQL General Mailing List
>For list archives:
>To unsubscribe:

efficient use of varchar?John G. Heim6 Apr
  • Re: efficient use of varchar?petya6 Apr
    • RE: efficient use of varchar?Jerry Schwartz7 Apr
      • RE: efficient use of varchar?Jerry Schwartz7 Apr