Solved

Which to use to create a temporary table in mysql

Posted on 2011-09-29
3
467 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

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Suggested Solutions

Read about why website design really matters in today's demanding market.
Any business that wants to seriously grow needs to keep the needs and desires of an international audience of their websites in mind. Making a website friendly to international users isn’t prohibitively expensive and can provide an incredible return…
Viewers will get an overview of the benefits and risks of using Bitcoin to accept payments. What Bitcoin is: Legality: Risks: Benefits: Which businesses are best suited?: Other things you should know: How to get started:
Any person in technology especially those working for big companies should at least know about the basics of web accessibility. Believe it or not there are even laws in place that require businesses to provide such means for the disabled and aging p…

706 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now