By the way, the weird-looking title is in Japanese (the database/table/field
Some of you might be able to read that.
Is it possible that this is upsetting the ANALYSE procedure?
Global Information Incorporated
195 Farmington Ave.
Farmington, CT 06032
860.674.8796 / FAX: 860.674.8341
Web site: www.the-infoshop.com
>From: Jerry Schwartz [mailto:jerry@stripped]
>Sent: Thursday, April 07, 2011 12:09 PM
>To: 'petya'; 'John G. Heim'
>Subject: RE: efficient use of varchar?
>>From: petya [mailto:petya@stripped]
>>Sent: Wednesday, April 06, 2011 3:55 PM
>>To: John G. Heim
>>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: giiexpr_db.prod.prod_title
>High Throughput Screening 2003: Improving Strategies, Technologies, and
> Min_length: 2
> Max_length: 255
> Empties_or_zeros: 0
> Nulls: 0
> 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
>Global Information Incorporated
>195 Farmington Ave.
>Farmington, CT 06032
>860.674.8796 / FAX: 860.674.8341
>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.
>>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=1
>MySQL General Mailing List
>For list archives: http://lists.mysql.com/mysql
>To unsubscribe: http://lists.mysql.com/mysql?unsub=1