RE: efficient use of varchar?
-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
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: je...@gii.co.jp Web site: www.the-infoshop.com -Original Message- From: Jerry Schwartz [mailto:je...@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: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=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
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=arch...@jab.org
