Solved

Which to use to create a temporary table in mysql

Posted on 2011-09-29
3
470 Views
Last Modified: 2012-05-12
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
Comment
Question by:Squadless
3 Comments
 
LVL 17

Accepted Solution

by:
ram_0218 earned 500 total points
ID: 36815072
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
 
LVL 24

Expert Comment

by:johanntagle
ID: 36815089
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
 
LVL 29

Expert Comment

by:Olaf Doschke
ID: 36815526
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

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Read about why website design really matters in today's demanding market.
Does your audience prefer people in photos or no people? How can you best highlight what you’re selling? What are your competitors doing, and what can you do that is different and unique from them?  Continue reading to learn how to make your images …
The viewer will get a basic understanding of what section 508 compliance can entail, learn about skip navigation links, alt text, transcripts, and font size controls.
Video by: Mark
This lesson goes over how to construct ordered and unordered lists and how to create hyperlinks.

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question