Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Which to use to create a temporary table in mysql

Posted on 2011-09-29
3
Medium Priority
?
485 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 17

Accepted Solution

by:
ram_0218 earned 2000 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 30

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

Understanding Web Applications

Without even knowing it, most of us are using web applications on a daily basis. Gmail and Yahoo email, Twitter, Facebook, and eBay are used by most of us daily—and they are web applications. We often confuse these web applications tools for websites.  So, what is the difference?

Question has a verified solution.

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

In this series, we will discuss common questions received as a database Solutions Engineer at Percona. In this role, we speak with a wide array of MySQL and MongoDB users responsible for both extremely large and complex environments to smaller singl…
Ready to get certified? Check out some courses that help you prepare for third-party exams.
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.
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).

705 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