>-----Original Message-----
>From: petya [mailto:[email protected]]
>Sent: Wednesday, April 06, 2011 3:55 PM
>To: John G. Heim
>Cc: [email protected]
>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: [email protected]
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/[email protected]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[email protected]
