>-----Original Message----- >From: petya [mailto:pe...@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: je...@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=je...@gii.co.jp -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org