Solved

Which to use to create a temporary table in mysql

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Although it can be difficult to imagine, someday your child will have a career of his or her own. He or she will likely start a family, buy a home and start having their own children. So, while being a kid is still extremely important, it’s also …
Introduction This article is intended for those who are new to PHP error handling (https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html).  It addresses one of the most common problems that plague beginning PHP develop…
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:
Learn how to create flexible layouts using relative units in CSS.  New relative units added in CSS3 include vw(viewports width), vh(viewports height), vmin(minimum of viewports height and width), and vmax (maximum of viewports height and width).

910 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

22 Experts available now in Live!

Get 1:1 Help Now