List:General Discussion« Previous MessageNext Message »
From:Heikki Tuuri Date:October 21 2004 2:02pm
Subject:Re: Char to Varchar on Innodb
View as plain text  
Gary,

----- Alkuperäinen viesti ----- 
Lähettäjä: "Gary Richardson" <gary.richardson@stripped>
Vastaanottaja: "Heikki Tuuri" <heikki.tuuri@stripped>
Kopio: <mysql@stripped>
Lähetetty: Thursday, October 21, 2004 4:49 AM
Aihe: Re: Char to Varchar on Innodb


> I'm not too worried about it myself, but I can see how less magic on a
> database server is better.
>
> Without actually trying it, if I mix CHAR's and VARCHAR's in a table,
> does SHOW CREATE TABLE reflect the internal conversion?
>
> If you wanted to reduce fragmentation in an Innodb table, wouldn't all
> the CHAR fields need to be left-packed? For example, you'd still get

if the field to be UPDATEd is fixed size, then an UPDATE does not cause
fragmentation. That is what I meant.

> fragmentation if your columns went CHAR, VARCHAR, CHAR. I guess that
> is unless the engine underneath did this automagically.
>
> Thanks.

Best regards,

Heikki
Innobase Oy
InnoDB - transactions, row level locking, and foreign keys for MySQL
InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM
tables
http://www.innodb.com/order.php

Order MySQL support from http://www.mysql.com/support/index.html


> On Wed, 20 Oct 2004 11:29:30 +0300, Heikki Tuuri
> <heikki.tuuri@stripped> wrote:
> > Gary,
> >
> > those 'silent column specification changes' affect all table types. That
> > feature has caused lots of user questions over years. It is in the TODO
to
> > remove that feature from MySQL, because it is not standards compliant.
In
> > InnoDB, a reason to use a CHAR column in some cases is to reduce
> > fragmentation if there are lots of updates to that column. A CHAR column
> > takes a fixed space. Silent column specification changes in many cases
> > defeat this optimization which would otherwise be available to users.
> >
> > Best regards,
> >
> > Heikki Tuuri
> > Innobase Oy
> > Foreign keys, transactions, and row level locking for MySQL
> > InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up
MyISAM
> > tables
> > http://www.innodb.com/order.php
> >
> > Order MySQL technical support from https://order.mysql.com/
> >
> > ....................
> >
> >
> > Hey,
> >
> > From http://dev.mysql.com/doc/mysql/en/Silent_column_changes.html:
> >
> > <snip>
> > If any column in a table has a variable length, the entire row becomes
> > variable-length as a result. Therefore, if a table contains any
> > variable-length columns (VARCHAR, TEXT, or BLOB), all CHAR columns
> > longer than three characters are changed to VARCHAR columns. This
> > doesn't affect how you use the columns in any way; in MySQL, VARCHAR
> > is just a different way to store characters. MySQL performs this
> > conversion because it saves space and makes table operations faster.
> > See section 15 MySQL Storage Engines and Table Types.
> > </snip>
> >
> > Does this affect all table types? I'm curious if this is happening on
> > my InnoDB tables as well. No problems, just curiosity..
> >
> > Thanks.
> >
> >
> > --
> > MySQL General Mailing List
> > For list archives: http://lists.mysql.com/mysql
> > To unsubscribe:
http://lists.mysql.com/mysql?unsub=1
> >
> >

Thread
Char to Varchar on InnodbGary Richardson19 Oct
Re: Char to Varchar on InnodbHeikki Tuuri20 Oct
  • Re: Char to Varchar on InnodbMartijn Tonies20 Oct
  • Re: Char to Varchar on InnodbGary Richardson21 Oct
  • Re: Char to Varchar on InnodbHeikki Tuuri21 Oct