Professional Web Applications Themes

Major diff between changing a VARCHAR to 70 or 100? - MySQL

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 ...

  1. #1

    Default 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


    Laphan Guest

  2. #2

    Default Re: Major diff between changing a VARCHAR to 70 or 100?

    >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
    Gordon Burditt Guest

  3. #3

    Default Re: Major diff between changing a VARCHAR to 70 or 100?

    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.lqtlaburditt.org> wrote in message
    news:11mkrh654q7lhd6corp.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


    Laphan Guest

  4. #4

    Default Re: Major diff between changing a VARCHAR to 70 or 100?

    In article <11ml7ctkk8g3399corp.supernews.com>,
    "Laphan" <infoSpamMeNot.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;
    Harald Fuchs Guest

  5. #5

    Default Re: Major diff between changing a VARCHAR to 70 or 100?


    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 |
    +----+-------+

















    Gazelem Guest

  6. #6

    Default Re: Major diff between changing a VARCHAR to 70 or 100?

    "Laphan" <infoSpamMeNot.co.uk> wrote in
    news:11ml7ctkk8g3399corp.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.

    *****

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

    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.

    Good Man Guest

  7. #7

    Default Re: Major diff between changing a VARCHAR to 70 or 100?

    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" <heyholetsgo.com> wrote in message
    news:Xns9704A607FFCB8sonicyouth216.196.97.131...
    "Laphan" <infoSpamMeNot.co.uk> wrote in
    news:11ml7ctkk8g3399corp.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.

    *****

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

    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.


    Laphan Guest

  8. #8

    Default Re: Major diff between changing a VARCHAR to 70 or 100?

    In article <11mni6mhqnd2ad0news.supernews.com>,
    "Gazelem" <usenetenhanced.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.
    Harald Fuchs Guest

  9. #9

    Default Re: Major diff between changing a VARCHAR to 70 or 100?

    >> 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
    Gordon Burditt Guest

  10. #10

    Default Re: Major diff between changing a VARCHAR to 70 or 100?

    In article <11mpunradsa5ma5corp.supernews.com>,
    [email]gordonb.s920xburditt.org[/email] (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.
    Harald Fuchs Guest

  11. #11

    Default Re: Major diff between changing a VARCHAR to 70 or 100?

    "Harald Fuchs" <hf0923xprotecting.net> wrote in message
    news:87r79w4stx.fsfsrv.protecting.net...
    > In article <11ml7ctkk8g3399corp.supernews.com>,
    > "Laphan" <infoSpamMeNot.co.uk> writes:
    >
    <SNIP SOME>
    > 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.
    <SNIP TO END>

    Correct me if I am wrong, but I do not believe that is _not_ a "MySQL"
    dependent feature; rather it is a user selected pluggable database version
    issue.
    As I understand it, some of the engines will recoil and cancel transaction
    (like INNODB).

    ~ Duane Phillips.


    Duane Phillips Guest

  12. #12

    Default Re: Major diff between changing a VARCHAR to 70 or 100?

    In article <JsWdne1ILOjJL_LeRVn-hwgiganews.com>,
    "Duane Phillips" <askmeaskme.askme> writes:
    > "Harald Fuchs" <hf0923xprotecting.net> wrote in message
    > news:87r79w4stx.fsfsrv.protecting.net...
    >> In article <11ml7ctkk8g3399corp.supernews.com>,
    >> "Laphan" <infoSpamMeNot.co.uk> writes:
    >>
    > <SNIP SOME>
    >> 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.
    > <SNIP TO END>
    > Correct me if I am wrong, but I do not believe that is _not_ a "MySQL"
    > dependent feature; rather it is a user selected pluggable database version
    > issue.
    > As I understand it, some of the engines will recoil and cancel transaction
    > (like INNODB).
    Unfortunately it's a frontend issue - I destroyed most of my data by
    erroneously doing a

    ALTER TABLE mytbl MODIFY mycol TEXT NOT NULL

    where mytbl was an InnoDB table. mycol originally was MEDIUMTEXT and
    I only wanted to set the NOT NULL attribute.

    Even MySQL 5.0.15 with "sql_mode = 'traditional'" does not prevent that.
    Harald Fuchs Guest

  13. #13

    Default Re: Major diff between changing a VARCHAR to 70 or 100?

    Harald Fuchs wrote:
    > In article <JsWdne1ILOjJL_LeRVn-hwgiganews.com>,
    > "Duane Phillips" <askmeaskme.askme> writes:
    >
    >
    >>"Harald Fuchs" <hf0923xprotecting.net> wrote in message
    >>news:87r79w4stx.fsfsrv.protecting.net...
    >>
    >>>In article <11ml7ctkk8g3399corp.supernews.com>,
    >>>"Laphan" <infoSpamMeNot.co.uk> writes:
    >>>
    >>
    >><SNIP SOME>
    >>
    >>>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.
    >>
    >><SNIP TO END>
    >
    >
    >>Correct me if I am wrong, but I do not believe that is _not_ a "MySQL"
    >>dependent feature; rather it is a user selected pluggable database version
    >>issue.
    >>As I understand it, some of the engines will recoil and cancel transaction
    >>(like INNODB).
    >
    >
    > Unfortunately it's a frontend issue - I destroyed most of my data by
    > erroneously doing a
    >
    > ALTER TABLE mytbl MODIFY mycol TEXT NOT NULL
    >
    > where mytbl was an InnoDB table. mycol originally was MEDIUMTEXT and
    > I only wanted to set the NOT NULL attribute.
    >
    > Even MySQL 5.0.15 with "sql_mode = 'traditional'" does not prevent that.

    So, what's the problem? It did exactly what you told it to - like it
    should.

    I you have valuable data, just ensure you backup the table before you
    alter it!


    --
    ==================
    Remove the "x" from my email address
    Jerry Stuckle
    JDS Computer Training Corp.
    [email]jstucklexattglobal.net[/email]
    ==================
    Jerry Stuckle Guest

  14. #14

    Default Re: Major diff between changing a VARCHAR to 70 or 100?

    Laphan wrote:
    > 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
    >
    >
    It will add 3 bytes to each row in the table.

    --
    ==================
    Remove the "x" from my email address
    Jerry Stuckle
    JDS Computer Training Corp.
    [email]jstucklexattglobal.net[/email]
    ==================
    Jerry Stuckle Guest

Similar Threads

  1. Would u know, what is diff b/w C#.net and Vb.net
    By koti_1mca@rediffmail.com in forum ASP.NET Web Services
    Replies: 1
    Last Post: April 4th, 09:12 AM
  2. Replies: 0
    Last Post: October 28th, 04:16 PM
  3. VARCHAR or not ?
    By Dirk Moolman in forum Informix
    Replies: 3
    Last Post: September 9th, 08:07 PM
  4. PHP Diff Module
    By Why? in forum PHP Development
    Replies: 1
    Last Post: August 21st, 10:04 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139