List:General Discussion« Previous MessageNext Message »
From:Richard Ellerbrock Date:August 22 2000 7:31am
Subject:Re: Temp tables and varchar to char silent conversion
View as plain text  
>
>Hi!
>
>>>>>> "Richard" == Richard Ellerbrock <richarde@stripped>
> writes:
>
>Richard> I use temporary tables very often in my applications and have 
>noticed with 3.23.20 that when columns are selected into a temporary table 
>using:
>Richard> CREATE TEMPORARY TABLE xxx SELECT * from yyy;
>
>Richard> all varchar columns are silently converted to char columns. The 
>nature of char columns being fixed length, this could impose a HUGE tax on 
>temp table size as demonstrated in the following directory listing:
>
>Richard> [root@bart /tmp]# ls -al *.M*
>Richard> -rw-rw----    1 mysql    mysql    19342266 Aug 21 20:10 
>#sql57f5_62b19_0.MYD
>
>Richard> [root@bart /tmp]# cd /var/lib/mysql/nds
>Richard> [root@bart nds]# ls -al nds.*
>Richard> -rw-rw----    1 mysql    mysql     5739372 Aug 18 16:35 nds.MYD
>
>Richard> Note the massive increase in table size due to many varchar fields 
>being used in the main database. This is on a table with around 20k rows. I 
>do similar tricks with tables with over 3mil rows. I shudder to think what 
>the disk space tax would be. Also note the added disk IO of writing the 
>extra 75% disk space. Also, what happens when temporary files are 
>automatically created with big selects? Are columns also converted to char?
>
>Richard> Could this not be considered a significant performance improvement 
>factoring in that disks are the slowest component of the overall system?
>
>The problem is that MySQL doesn't really know how many rows you will
>insert in the temporary table;  As fixed size rows are much faster
>than dynamic size rows, this is the default.
>
>Fortunately this is very easy to change:
>
>Just use the row_format=dynamic option to create table!

Thanks for the info. I see that dynamic row_format was added in 3.23.6 already! Cannot
believe that I missed that. Suppose I should RTFM from top to bottom each time I upgrade
:-)

--
Richard Ellerbrock
richarde@stripped

Thread
replace problems...Eric D. Patterson15 Mar
  • replace problems...Michael Widenius15 Mar
  • Temp tables and varchar to char silent conversionMichael Widenius21 Aug
  • Re: MySQL 5.0.4-beta has been releasedJosh Trutwin18 Apr
    • Re: MySQL 5.0.4-beta has been releasedJoerg Bruehe18 Apr
      • Re: MySQL 5.0.4-beta has been releasedJosh Trutwin18 Apr
  • Re: MySQL 5.0.4-beta has been releasedMartijn Tonies19 Apr
  • Re: MySQL 5.0.4-beta has been releasedFredrick Bartlett19 Apr
    • Re: MySQL 5.0.4-beta has been releasedReggie Burnett19 Apr
  • Re: MySQL 5.0.4-beta has been releasedFredrick Bartlett19 Apr
Re: Temp tables and varchar to char silent conversionRichard Ellerbrock22 Aug