Re: Temporary tables - MySQL 4.1 alpha
Nils Valentin [EMAIL PROTECTED] wrote: I have some questions about temporary tables. I would appreciate any replies: I created a temporary table f.e. like this: mysql create temporary table temp SELECT * FROM relations; Query OK, 4 rows affected (2.35 sec) Records: 4 Duplicates: 0 Warnings: 0 When I do mysql show create table temp; Then it will give me this: | temp | CREATE TEMPORARY TABLE `temp` ( `member_id` int(16) NOT NULL default '0', `company_id` int(16) NOT NULL default '0', `membership_id` int(16) NOT NULL default '0' ) TYPE=MyISAM CHARSET=latin1 | Now what confuses me is that a) it says here TYPE=MyISAM Because MyISAM is default table type b) I understood that only HEAP tables are stored in the memory. Yes. I was assuming (until now) that HEAP tables are (the only one type of) temporary tables. Temporary tables may have type HEAP. I tried CREATE TEMPORARY TABLE temp TYPE= InnoDB SELECT * FROM relations; or CREATE TEMPORARY TABLE temp TYPE= HEAP SELECT * FROM relations; and they are all created as in-memory tables no files are created. Can I use any table format (ISAM,MYISAM,BDB,InnoDB,HEAP) for temporary tables You can use MyISAM, ISAM, MERGE, HEAP, InnoDB. Temporary table files are created in the temporary directory: http://www.mysql.com/doc/en/Temporary_files.html If, so what would be the difference between a temporary table in general and a HEAP table ? Temporary tables are the per-connection tables. HEAP tables are stored in memory tables. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Temporary tables - MySQL 4.1 alpha
WOW Victoria, thats a good explanation. That makes many things clearer now. Thank you very much. However, when not having the set the tmpdir variable were are the temporary tables stored ? (I searched the whole harddisc) I assume in this case that they must be stored in the memory, is that right ? Best regards Nils Valentin Tokyo/Japan 2003 6 25 19:17Victoria Reznichenko : Nils Valentin [EMAIL PROTECTED] wrote: I have some questions about temporary tables. I would appreciate any replies: I created a temporary table f.e. like this: mysql create temporary table temp SELECT * FROM relations; Query OK, 4 rows affected (2.35 sec) Records: 4 Duplicates: 0 Warnings: 0 When I do mysql show create table temp; Then it will give me this: | temp | CREATE TEMPORARY TABLE `temp` ( `member_id` int(16) NOT NULL default '0', `company_id` int(16) NOT NULL default '0', `membership_id` int(16) NOT NULL default '0' ) TYPE=MyISAM CHARSET=latin1 | Now what confuses me is that a) it says here TYPE=MyISAM Because MyISAM is default table type b) I understood that only HEAP tables are stored in the memory. Yes. I was assuming (until now) that HEAP tables are (the only one type of) temporary tables. Temporary tables may have type HEAP. I tried CREATE TEMPORARY TABLE temp TYPE= InnoDB SELECT * FROM relations; or CREATE TEMPORARY TABLE temp TYPE= HEAP SELECT * FROM relations; and they are all created as in-memory tables no files are created. Can I use any table format (ISAM,MYISAM,BDB,InnoDB,HEAP) for temporary tables You can use MyISAM, ISAM, MERGE, HEAP, InnoDB. Temporary table files are created in the temporary directory: http://www.mysql.com/doc/en/Temporary_files.html If, so what would be the difference between a temporary table in general and a HEAP table ? Temporary tables are the per-connection tables. HEAP tables are stored in memory tables. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- --- Valentin Nils Internet Technology E-Mail: [EMAIL PROTECTED] URL: http://www.knowd.co.jp Personal URL: http://www.knowd.co.jp/staff/nils -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Temporary tables - MySQL 4.1 alpha
Nils Valentin [EMAIL PROTECTED] wrote: WOW Victoria, thats a good explanation. That makes many things clearer now. Thank you very much. However, when not having the set the tmpdir variable were are the temporary tables stored ? (I searched the whole harddisc) I assume in this case that they must be stored in the memory, is that right ? Nope. Temporary tables are stored in memory only if table type is HEAP. All other temporary tables are stored on disk. By default temporary dir is /tmp or /usr/tmp. You can check it with SHOW VARIABLES LIKE 'tmpdir'; Name of temporary table files looks like #sql2884_b_0.frm. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Temporary tables - MySQL 4.1 alpha
Hi Vitcoria, Thank you for the response. No more questions about this, all systems clear now ;-). Thank you so much. Nils Valentin Tokyo/Japan 2003 6 25 21:18Victoria Reznichenko : Nils Valentin [EMAIL PROTECTED] wrote: WOW Victoria, thats a good explanation. That makes many things clearer now. Thank you very much. However, when not having the set the tmpdir variable were are the temporary tables stored ? (I searched the whole harddisc) I assume in this case that they must be stored in the memory, is that right ? Nope. Temporary tables are stored in memory only if table type is HEAP. All other temporary tables are stored on disk. By default temporary dir is /tmp or /usr/tmp. You can check it with SHOW VARIABLES LIKE 'tmpdir'; Name of temporary table files looks like #sql2884_b_0.frm. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- --- Valentin Nils Internet Technology E-Mail: [EMAIL PROTECTED] URL: http://www.knowd.co.jp Personal URL: http://www.knowd.co.jp/staff/nils -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
