List:General Discussion« Previous MessageNext Message »
From:Michael Widenius Date:August 21 2000 8:35pm
Subject: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!

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