Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 494
  • Last Modified:

Which to use to create a temporary table in mysql

Hi,
Im creating temporary tables in mysql.
I have 2 choices, using

create temporary table A ...

vs

create table .....   ENGINE = MEMORY;

What is the difference between two approaches??
0
Squadless
Asked:
Squadless
1 Solution
 
ram_0218Commented:
the first solution drops the table as soon as the DB connection is closed, second solution drops table when the mysql instance is restarted
0
 
johanntagleCommented:
Just a small correction to ram_0218's post - when using Memory Engine the contents of the table is emptied when the MySQL instance is stopped/restarted, but the table itself is not dropped.  You will find the table exists but is empty after you restart.
0
 
Olaf DoschkeSoftware DeveloperCommented:
Besides this, there are more important differences.

The main one is the differing visibility: A MEMORY table is visible to anyonw like any normal table, a temporary tables is not only limited to the current connection in it's lifetime, but also in it's visibility. You can create a temp table with the same name from two clients and each one will only see his own table and data, therefore it's better to use for data session AND user/client specific.

ENGINE=MEMORY also means the data of that table is held in memory only, and RAM (including Swap AFAIK) is the limiting factor for such tables. What is saved on disc is a frm file, which just contains the table definition/structure, but no data. That also is the reason the memory tables are not lost even after a restart.

For their differing visibility the one or other type of tables are not interchangable. If you take into account all users/seession sharing MEMORY data you can use a MEMORY table as substitute for a temp table, but if data needs to be public available, a temp table is not able to replace a MEMORY table.

Some downsides of MEMORY tables especially in comparison with MySQL Cluster and performancewise, are discussed in the reference at http://dev.mysql.com/doc/refman/5.1/en/memory-storage-engine.html

Bye, Olaf.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now