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