From: Michael Widenius
Date: August 21 2000 8:35pm
Subject: Temp tables and varchar to char silent conversion
List-Archive: http://lists.mysql.com/mysql/48483
Message-Id: <14753.37533.123809.334730@narttu.mysql.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit


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!

Here is an example of this:

mysql> create table t1 select 1,"hello";
Query OK, 1 row affected (0.01 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> show table status like "t1";
+------+--------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+----------------+---------+
| Name | Type   | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time         | Update_time         | Check_time | Create_options | Comment |
+------+--------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+----------------+---------+
| t1   | MyISAM | Fixed      |    1 |             14 |          14 |      2147483647 |         1024 |         0 |           NULL | 2000-08-21 23:29:27 | 2000-08-21 23:29:27 | NULL       |                |         |
+------+--------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+----------------+---------+
1 row in set (0.01 sec)

mysql> drop table t1;
Query OK, 0 rows affected (0.00 sec)

mysql> create table t1 row_format=dynamic select 1,"hello";
Query OK, 1 row affected (0.01 sec)
Records: 1  Duplicates: 0  Warnings: 0
mysql> show table status like "t1";
+------+--------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+----------------+---------+
| Name | Type   | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time         | Update_time         | Check_time | Create_options | Comment |
+------+--------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+----------------+---------+
| t1   | MyISAM | Dynamic    |    1 |             20 |          20 |      2147483647 |         1024 |         0 |           NULL | 2000-08-21 23:29:58 | 2000-08-21 23:29:58 | NULL       | format=DYNAMIC |         |
+------+--------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+----------------+---------+
1 row in set (0.00 sec)


Just check the difference in Row_format.

Regards,
Monty