Major diff between changing a VARCHAR to 70 or 100?

Hi All

One of my VarChar fields in my MySQL DB isn't big enough so I need to expand
it. In light of this, could you please answer these queries:

1) How do I expand the size of the field without affecting the actual data
inside the field?

2) As I like to try and keep things as tight as possible in the DB, is there
any REAL differences between changing my VarChar field from it's current
setting of 30 to 70 or 100? What I'm saying is, if I set it to 100 am I
losing out on anything over setting it to 70, eg search speed, physical db
size, etc?

Thanks

Laphan

>One of my VarChar fields in my MySQL DB isn't big enough so I need to expand
>it. In light of this, could you please answer these queries:
>
>1) How do I expand the size of the field without affecting the actual data
>inside the field?

ALTER TABLE tablename change fieldname fieldname varchar(100) not null;

(no, having fieldname in there twice is not a typo. One is the old
field name and the other is the new one).

>2) As I like to try and keep things as tight as possible in the DB, is there
>any REAL differences between changing my VarChar field from it's current
>setting of 30 to 70 or 100? What I'm saying is, if I set it to 100 am I
>losing out on anything over setting it to 70, eg search speed, physical db
>size, etc?

If it can't hold your data, does speed matter? If you can retrieve
and process 100 records incorrectly per second, is that better than
processing 100 records correctly in 72 hours (the time needed to
fix the code)?

There may be differences *OUTSIDE* the database, such as the size of
buffers used to hold the contents of the field (in C or C++, for
example), and you should be doing length-checking on the input before
inserting it: that check needs to change. The width of HTML input
fields may also need to change.

There are some boundaries which do matter: I believe varchar can't
go over 255, at which point you need to switch to "text" or "longtext"
fields..

Gordon L. Burditt

Hi Gordon

Many thanks for the feedback.

The DB is purely used as the store for an ASP driven web site so apart from
the HTML input field everything is rosy in this respect.

I do try and keep my field sizes tight rather than every varchar being 255
chars long, but I didn't know if 100 chars slows a query down or is bigger
in size DB wise than 70 chars. Although 70 would cover it 100 would
definitely do it, but if it means any kind of degradation then I'll plumb
for 70.

Is there any kind of noticeable diff?

Rgds Laphan


"Gordon Burditt" <gordonb.lqtla@burditt.org> wrote in message
news:11mkrh654q7lhd6@corp.supernews.com...
>One of my VarChar fields in my MySQL DB isn't big enough so I need to
>expand
>it. In light of this, could you please answer these queries:
>
>1) How do I expand the size of the field without affecting the actual data
>inside the field?

ALTER TABLE tablename change fieldname fieldname varchar(100) not null;

(no, having fieldname in there twice is not a typo. One is the old
field name and the other is the new one).

>2) As I like to try and keep things as tight as possible in the DB, is
>there
>any REAL differences between changing my VarChar field from it's current
>setting of 30 to 70 or 100? What I'm saying is, if I set it to 100 am I
>losing out on anything over setting it to 70, eg search speed, physical db
>size, etc?

If it can't hold your data, does speed matter? If you can retrieve
and process 100 records incorrectly per second, is that better than
processing 100 records correctly in 72 hours (the time needed to
fix the code)?

There may be differences *OUTSIDE* the database, such as the size of
buffers used to hold the contents of the field (in C or C++, for
example), and you should be doing length-checking on the input before
inserting it: that check needs to change. The width of HTML input
fields may also need to change.

There are some boundaries which do matter: I believe varchar can't
go over 255, at which point you need to switch to "text" or "longtext"
fields..

Gordon L. Burditt

In article <11ml7ctkk8g3399@corp.supernews.com>,
"Laphan" <info@SpamMeNot.co.uk> writes:

> I do try and keep my field sizes tight rather than every varchar being 255
> chars long, but I didn't know if 100 chars slows a query down or is bigger
> in size DB wise than 70 chars.

If you try to keep your field sizes tight, then you shouldn't use MySQL:
if you make one of your fields by accident somewhat too tight, MySQL
(at least before version 5) will silently destroy some of your data.
Try this:


CREATE TABLE t1 (
id SERIAL,
val VARCHAR(10) NOT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB;

INSERT INTO t1 (val) VALUES ('1234567890');
INSERT INTO t1 (val) VALUES ('12345');

ALTER TABLE t1 MODIFY val VARCHAR(5) NOT NULL;

SELECT * FROM t1;

That same proceedure done in mysql 3.23 works just fine.. As you can see
from the output below, there is no lost records, only one fields lost some
data, but obviously that is what we were trying to do right? or did I
misunderstand your example?



CREATE TABLE t1 (
id int(10) not null auto_increment,
val VARCHAR(10) NOT NULL,
PRIMARY KEY (id)
);
INSERT INTO t1 (val) VALUES ('1234567890');
INSERT INTO t1 (val) VALUES ('12345');

SELECT * FROM t1;
+----+------------+
| id | val |
+----+------------+
| 1 | 1234567890 |
| 2 | 12345 |
+----+------------+

ALTER TABLE t1 MODIFY val VARCHAR(5) NOT NULL;


Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 1

select * from t1;
+----+-------+
| id | val |
+----+-------+
| 1 | 12345 |
| 2 | 12345 |
+----+-------+

"Laphan" <info@SpamMeNot.co.uk> wrote in
news:11ml7ctkk8g3399@corp.supernews.com:

> Hi Gordon
>
> Many thanks for the feedback.
>
> The DB is purely used as the store for an ASP driven web site so apart
> from the HTML input field everything is rosy in this respect.
>
> I do try and keep my field sizes tight rather than every varchar being
> 255 chars long, but I didn't know if 100 chars slows a query down or
> is bigger in size DB wise than 70 chars. Although 70 would cover it
> 100 would definitely do it, but if it means any kind of degradation
> then I'll plumb for 70.

note that specifying the varchar length should refer to the maximum
amount of characters that will be stored in the column.

varchar is unique in that if you specify it be 255 characters long, and
only insert a value that is 8 characters long, it only records the 8
characters. This is in contrast to say "char", which will pad the
remaining 247 characters with spaces.

in other words, VARCHAR(255) will record 8 bytes of data for your 8
characters; CHAR(255) will record 255 bytes, regardless of the amount of
data in the column - 8 characters, 1 character, or 200 characters.

so, the answer to your question is that defining a varchar column with
255 characters will not make the table larger than a varchar column
defined as 15 characters - only the actual amount of data in the column
increases the size of the table/database. You have nothing to lose by
specifying your field to be 100 characters.

*****

http://dev.mysql.com/doc/refman/4.1/en/char.html

In contrast to CHAR, VARCHAR values are stored using only as many
characters as are needed, plus one byte to record the length (two bytes
for columns that are declared with a length longer than 255).

VARCHAR values are not padded when they are stored. Trailing spaces in
MySQL version up to and including 4.1 are removed from values when stored
in a VARCHAR column; this also means that the spaces are absent from
retrieved values.

Hi Guys

Many thanks for the feedback.

I know this is a little off topic, but I use decimal types for my price
figures is there any problem in expanding these to say (13,6) when they are
currently (10,3).

Problems in that I am talking file size, query performance, etc.

Thanks


"Good Man" <heyho@letsgo.com> wrote in message
news:Xns9704A607FFCB8sonicyouth@216.196.97.131...
"Laphan" <info@SpamMeNot.co.uk> wrote in
news:11ml7ctkk8g3399@corp.supernews.com:

> Hi Gordon
>
> Many thanks for the feedback.
>
> The DB is purely used as the store for an ASP driven web site so apart
> from the HTML input field everything is rosy in this respect.
>
> I do try and keep my field sizes tight rather than every varchar being
> 255 chars long, but I didn't know if 100 chars slows a query down or
> is bigger in size DB wise than 70 chars. Although 70 would cover it
> 100 would definitely do it, but if it means any kind of degradation
> then I'll plumb for 70.

note that specifying the varchar length should refer to the maximum
amount of characters that will be stored in the column.

varchar is unique in that if you specify it be 255 characters long, and
only insert a value that is 8 characters long, it only records the 8
characters. This is in contrast to say "char", which will pad the
remaining 247 characters with spaces.

in other words, VARCHAR(255) will record 8 bytes of data for your 8
characters; CHAR(255) will record 255 bytes, regardless of the amount of
data in the column - 8 characters, 1 character, or 200 characters.

so, the answer to your question is that defining a varchar column with
255 characters will not make the table larger than a varchar column
defined as 15 characters - only the actual amount of data in the column
increases the size of the table/database. You have nothing to lose by
specifying your field to be 100 characters.

*****

http://dev.mysql.com/doc/refman/4.1/en/char.html

In contrast to CHAR, VARCHAR values are stored using only as many
characters as are needed, plus one byte to record the length (two bytes
for columns that are declared with a length longer than 255).

VARCHAR values are not padded when they are stored. Trailing spaces in
MySQL version up to and including 4.1 are removed from values when stored
in a VARCHAR column; this also means that the spaces are absent from
retrieved values.

In article <11mni6mhqnd2ad0@news.supernews.com>,
"Gazelem" <usenet@enhanced.org> writes:

> That same proceedure done in mysql 3.23 works just fine.. As you can see
> from the output below, there is no lost records, only one fields lost some
> data, but obviously that is what we were trying to do right?

My point was that that the DB should disallow an ALTER TABLE if there
are column values longer than the new length specification. Silently
"losing some data" is not what a DB should do.

>> That same proceedure done in mysql 3.23 works just fine.. As you can see
>> from the output below, there is no lost records, only one fields lost some
>> data, but obviously that is what we were trying to do right?
>
>My point was that that the DB should disallow an ALTER TABLE if there
>are column values longer than the new length specification. Silently
>"losing some data" is not what a DB should do.

Then there should be an ALTER TABLE DAMMIT (or perhaps ALTER TABLE IGNORE)
to allow doing it anyway. *silently* losing data is bad. Refusing
to make a necessary change is also bad.

Gordon L. Burditt

In article <11mpunradsa5ma5@corp.supernews.com>,
gordonb.s920x@burditt.org (Gordon Burditt) writes:

>> My point was that that the DB should disallow an ALTER TABLE if there
>> are column values longer than the new length specification. Silently
>> "losing some data" is not what a DB should do.

> Then there should be an ALTER TABLE DAMMIT (or perhaps ALTER TABLE IGNORE)
> to allow doing it anyway. *silently* losing data is bad. Refusing
> to make a necessary change is also bad.

You don't need an ALTER TABLE IGNORE - just do an

UPDATE mytbl SET col = left (col, <newlen>)

before ALTER TABLE.