From: Richard Ellerbrock
Date: August 22 2000 7:31am
Subject: Re: Temp tables and varchar to char silent conversion
List-Archive: http://lists.mysql.com/mysql/48523
Message-Id: <s9a2487b.058@eskom.co.za>
MIME-Version: 1.0
Content-Type: text/plain; charset=US-ASCII
Content-Transfer-Encoding: quoted-printable

>
>Hi!
>
>>>>>> "Richard" =3D=3D Richard Ellerbrock <richarde@stripped> writes:
>
>Richard> I use temporary tables very often in my applications and have=20
>noticed with 3.23.20 that when columns are selected into a temporary =
table=20
>using:
>Richard> CREATE TEMPORARY TABLE xxx SELECT * from yyy;
>
>Richard> all varchar columns are silently converted to char columns. =
The=20
>nature of char columns being fixed length, this could impose a HUGE tax =
on=20
>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=20
>#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=20
>being used in the main database. This is on a table with around 20k rows. =
I=20
>do similar tricks with tables with over 3mil rows. I shudder to think =
what=20
>the disk space tax would be. Also note the added disk IO of writing =
the=20
>extra 75% disk space. Also, what happens when temporary files are=20
>automatically created with big selects? Are columns also converted to =
char?
>
>Richard> Could this not be considered a significant performance improvemen=
t=20
>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=3Ddynamic 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