Description:
This page:
http://dev.mysql.com/doc/refman/5.1/en/internal-temporary-tables.html
States:
'A temporary table that is created initially as a MEMORY table might be converted to a MyISAM table and stored on disk if it becomes too large.'
Note that this page is talking about *internal* (automatically created by mysqld when executing a query which needs a temporary table) tables.
Some issues here:
1. The 'internal-temporary-tables.html' page discusses MEMORY tables *and* internal tables. Maybe it should only discuss *internal* tables and not MEMORY (created with CREATE TABLE ... ENGINE=MEMORY) tables?
2. Maybe there should be a similar page for MEMORY tables, specifying only those items related specifically to MEMORY tables (i.e. not discussing internal tables, nor TEMPORARY (created with CREATE TEMPORARY) tables)
There are some further documentation considerations for CREATE TEMPORARY TABLE, for which I will log a separate bug.
How to repeat:
Example shows three different table types:
1. Internal temporary table
2. MEMORY table
3a. TEMPORARY table, in MEMORY
3b. TEMPORARY table, InnoDB
3c. TEMPORARY table, MyISAM
---
mysql> FLUSH STATUS;
Query OK, 0 rows affected (0.00 sec)
mysql> CREATE TABLE TESTDISK (ID INT) ENGINE=MYISAM;
Query OK, 0 rows affected (0.00 sec)
mysql> INSERT INTO TESTDISK VALUES (1),(2),(3),(4),(5),(8),(6),(7);
Query OK, 8 rows affected (0.00 sec)
Records: 8 Duplicates: 0 Warnings: 0
mysql> INSERT INTO TESTDISK SELECT A.ID FROM TESTDISK AS A, TESTDISK AS B, TESTDISK AS C, TESTDISK AS D;
Query OK, 4096 rows affected (0.02 sec)
Records: 4096 Duplicates: 0 Warnings: 0
mysql> SHOW STATUS LIKE '%tmp%';
+-------------------------+-------+
| Variable_name | Value |
+-------------------------+-------+
| Created_tmp_disk_tables | 0 |
| Created_tmp_files | 0 |
| Created_tmp_tables | 1 | /* Internal temporary table - to be discussed in internal-temporary-tables.html */
+-------------------------+-------+
3 rows in set (0.00 sec)
mysql> CREATE TABLE testmem (id int) ENGINE=MEMORY; /* Memory table - to be discussed in new page for memory tables */
Query OK, 0 rows affected (0.00 sec)
mysql> CREATE TEMPORARY TABLE testmem (id int) ENGINE=MEMORY; /* TEMPORARY tables - to be discussed in separate bug */
Query OK, 0 rows affected (0.00 sec)
mysql> CREATE TEMPORARY TABLE test2 (id int) ENGINE=InnoDB;
Query OK, 0 rows affected (0.01 sec)
mysql> CREATE TEMPORARY TABLE test3 (id int) ENGINE=MyISAM;
Query OK, 0 rows affected (0.00 sec)
---
