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
