---- mysql> SHOW GLOBAL VARIABLES LIKE 'tmpdir'; +---------------+-----------------+ | Variable_name | Value | +---------------+-----------------+ | tmpdir | C:\Windows\TEMP | +---------------+-----------------+ 1 row in set (0.00 sec) --- C:\Windows\Temp>dir #* Volume in drive C is SYS Volume Serial Number is 34A4-C4D1 Directory of C:\Windows\Temp File Not Found --- mysql> CREATE TEMPORARY TABLE test (id INT) ENGINE=MyISAM; FLUSH TABLES; Query OK, 0 rows affected (0.02 sec) Query OK, 0 rows affected (0.00 sec) --- c:\Windows\Temp>dir #* Volume in drive C is SYS Volume Serial Number is 34A4-C4D1 Directory of c:\Windows\Temp 30/11/2009 06:54 PM 8,556 #sql13b8_7_0.frm 30/11/2009 06:54 PM 0 #sql13b8_7_0.MYD 30/11/2009 06:54 PM 1,024 #sql13b8_7_0.MYI 3 File(s) 9,580 bytes 0 Dir(s) 34,624,569,344 bytes free --- mysql> INSERT INTO test VALUES (1),(2),(3); FLUSH TABLES; Query OK, 3 rows affected (0.00 sec) Records: 3 Duplicates: 0 Warnings: 0 --- c:\Windows\Temp>dir #* Volume in drive C is SYS Volume Serial Number is 34A4-C4D1 Directory of c:\Windows\Temp 30/11/2009 06:54 PM 8,556 #sql13b8_7_0.frm 30/11/2009 06:54 PM 0 #sql13b8_7_0.MYD /* no change */ 30/11/2009 06:54 PM 1,024 #sql13b8_7_0.MYI 3 File(s) 9,580 bytes 0 Dir(s) 34,624,569,344 bytes free /* no change > this points to the data being retained in-memory only */ --- mysql> CREATE TABLE copytest LIKE test; Query OK, 0 rows affected (0.01 sec) mysql> INSERT INTO copytest SELECT * FROM test; FLUSH TABLES; Query OK, 3 rows affected (0.00 sec) Records: 3 Duplicates: 0 Warnings: 0 Query OK, 0 rows affected (0.00 sec) --- c:\Windows\Temp>dir #* Volume in drive C is SYS Volume Serial Number is 34A4-C4D1 Directory of c:\Windows\Temp 30/11/2009 06:54 PM 8,556 #sql13b8_7_0.frm 30/11/2009 06:54 PM 0 #sql13b8_7_0.MYD 30/11/2009 06:54 PM 1,024 #sql13b8_7_0.MYI 3 File(s) 9,580 bytes 0 Dir(s) 34,624,557,056 bytes free /* Less space, since a MyISAM table was created in the data directory as a result of the LIKE copy (see bug #49192) - in either case, it does not matter for this example */ C:\mysql5140\data\roelt>dir copy* Volume in drive C is SYS Volume Serial Number is 34A4-C4D1 Directory of C:\mysql5140\data\roelt 30/11/2009 06:54 PM 8,556 copytest.frm 30/11/2009 06:55 PM 21 copytest.MYD 30/11/2009 06:55 PM 1,024 copytest.MYI 3 File(s) 9,601 bytes 0 Dir(s) 34,624,557,056 bytes free --- mysql> INSERT INTO test SELECT A.ID FROM copytest AS A, copytest AS B, copytest AS C, copytest AS D, copytest AS E, copytest AS F; Query OK, 729 rows affected (0.04 sec) Records: 729 Duplicates: 0 Warnings: 0 --- C:\Windows\Temp>dir #* Volume in drive C is SYS Volume Serial Number is 34A4-C4D1 Directory of C:\Windows\Temp 30/11/2009 06:54 PM 8,556 #sql13b8_7_0.frm 30/11/2009 06:54 PM 0 #sql13b8_7_0.MYD /* no change */ 30/11/2009 06:54 PM 1,024 #sql13b8_7_0.MYI 3 File(s) 9,580 bytes 0 Dir(s) 34,624,466,944 bytes free /* Notice that the diskspace has dropped > this points to in-memory data being cached/stored by Windows (not by mysqld, because then the total disk space would not have dropped, as per the example above - though mysqld would likely still have the impression the data is in-memory) */ --- mysql> INSERT INTO copytest SELECT * FROM test; Query OK, 732 rows affected (0.01 sec) Records: 732 Duplicates: 0 Warnings: 0 mysql> INSERT INTO test SELECT A.ID FROM copytest AS A, copytest AS B; Query OK, 540225 rows affected (0.23 sec) Records: 540225 Duplicates: 0 Warnings: 0 mysql> INSERT INTO test SELECT A.ID FROM copytest AS A, copytest AS B; Query OK, 540225 rows affected (0.14 sec) Records: 540225 Duplicates: 0 Warnings: 0 mysql> INSERT INTO test SELECT A.ID FROM copytest AS A, copytest AS B; Query OK, 540225 rows affected (0.14 sec) Records: 540225 Duplicates: 0 Warnings: 0 mysql> INSERT INTO test SELECT A.ID FROM copytest AS A, copytest AS B; Query OK, 540225 rows affected (0.14 sec) Records: 540225 Duplicates: 0 Warnings: 0 --- C:\Windows\Temp>dir copy* Volume in drive C is SYS Volume Serial Number is 34A4-C4D1 Directory of C:\Windows\Temp File Not Found C:\Windows\Temp>dir #* Volume in drive C is SYS Volume Serial Number is 34A4-C4D1 Directory of C:\Windows\Temp 30/11/2009 06:54 PM 8,556 #sql13b8_7_0.frm 30/11/2009 06:54 PM 0 #sql13b8_7_0.MYD /* Still no change */ 30/11/2009 06:54 PM 1,024 #sql13b8_7_0.MYI 3 File(s) 9,580 bytes 0 Dir(s) 34,609,262,592 bytes free /* Again, less diskspace (more Windows caching) */ --- mysql> INSERT INTO test SELECT A.ID FROM copytest AS A, copytest AS B; Query OK, 540225 rows affected (0.14 sec) Records: 540225 Duplicates: 0 Warnings: 0 --- C:\Windows\Temp>dir #* Volume in drive C is SYS Volume Serial Number is 34A4-C4D1 Directory of C:\Windows\Temp 30/11/2009 06:54 PM 8,556 #sql13b8_7_0.frm 30/11/2009 06:57 PM 18,912,999 #sql13b8_7_0.MYD /* Now data was written to the file */ 30/11/2009 06:54 PM 1,024 #sql13b8_7_0.MYI 3 File(s) 18,922,579 bytes 0 Dir(s) 34,604,937,216 bytes free ---