| Bug #66755 | Temporary table created on disk when it should be in ram | ||
|---|---|---|---|
| Submitted: | 10 Sep 2012 15:08 | Modified: | 11 Sep 2012 7:47 |
| Reporter: | Marco Facchini | Email Updates: | |
| Status: | Not a Bug | Impact on me: | |
| Category: | MySQL Server: Optimizer | Severity: | S4 (Feature request) |
| Version: | 5.5.19, 5.5.12 | OS: | Linux (Centos 5.8 & 6.2 ) |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | Created_tmp_tables, documentation, performance | ||
[11 Sep 2012 5:57]
Valeriy Kravchuk
From what I see:
C:\Program Files\MySQL\MySQL Server 5.5\bin>mysql -uroot -proot -P3312 test
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 13
Server version: 5.5.27 MySQL Community Server (GPL)
Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> CREATE TABLE test (
-> invoice_number varchar(100)
-> ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
Query OK, 0 rows affected (0.14 sec)
mysql>
mysql> insert into test values('a');
Query OK, 1 row affected (0.00 sec)
mysql> insert into test values('b');
Query OK, 1 row affected (0.00 sec)
mysql>
mysql> show status like 'cre%';
+-------------------------+-------+
| Variable_name | Value |
+-------------------------+-------+
| Created_tmp_disk_tables | 0 |
| Created_tmp_files | 7 |
| Created_tmp_tables | 0 |
+-------------------------+-------+
3 rows in set (0.00 sec)
mysql>
mysql> SELECT DISTINCT invoice_number FROM test\G
*************************** 1. row ***************************
invoice_number: a
*************************** 2. row ***************************
invoice_number: b
2 rows in set (0.02 sec)
mysql>
mysql> show status like 'cre%';
+-------------------------+-------+
| Variable_name | Value |
+-------------------------+-------+
| Created_tmp_disk_tables | 0 |
| Created_tmp_files | 7 |
| Created_tmp_tables | 1 |
+-------------------------+-------+
3 rows in set (0.00 sec)
I'd say that temporary table is created in memory on 5.5.27. Note that Created_tmp_disk_tables had NOT increased.
Description: In the following sequence of ddl and dml, the select distinct statement creates the table on disk, despite the documentation says this happens only when there is "presence of any column in a GROUP BY or DISTINCT clause larger than 512 bytes" it seems to me this happens even with less than 512 bytes How to repeat: CREATE TABLE test ( invoice_number varchar(100) ) ENGINE=MyISAM DEFAULT CHARSET=latin1; insert into test values('a'); insert into test values('b'); show status like 'cre%'; SELECT DISTINCT invoice_number FROM test\G show status like 'cre%'; drop table test;