List:General Discussion« Previous MessageNext Message »
From:Gary Richardson Date:October 21 2004 1:49am
Subject:Re: Char to Varchar on Innodb
View as plain text  
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
fragmentation if your columns went CHAR, VARCHAR, CHAR. I guess that
is unless the engine underneath did this automagically.

Thanks.

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