Difference between heap table and temporary table in MySQL?

Question

I am very new to MySQL. My question may wrong, if it is please correct or explain it.

I Just read about Heap table and temporary table by searching definition on Google. What is the exact difference between them and what real time use of both?

As per my knowledge or what I have read:

Heap table : Tables that are present in the memory are called as HEAP tables. When creating a HEAP table in MySql, user needs to specify the TYPE as HEAP. These tables are now more commonly known as memory tables. These memory tables never have values with data type like “BLOB” or “TEXT”. They use indexes which make them faster.

Temporary table : The temporary tables could be very useful in some cases to keep temporary data. Temporary table is that they will be deleted when the current client session terminates.

Answer 1

As you quoted yourself, temporary tables are only valid during the session while heap tables exist in memory. So a heap table can exist for a long time if you do not restart your Database.

The temporary table will be dropped as soon as your session disconnects.

Temporary tables are not shared among clients, heap tables are shared. So to each connection the temporary table is unique, for a second connection the temporary tables of another connection are not existant.

For temporary tables you need a special privilege (create temporary table) while heap tables are just another storage engine.

Answer 2

MySQL temporary tables (temp tables refers to the same thing) are tables that are scoped to the session in which they are created. You can create a temporary table of the same name as another temporary table created in another session, and they don’t conflict. Each session sees only their own temporary tables, and each table may have different data. As soon as your session closes, all temporary tables are dropped, and the data is removed.

MySQL also creates temporary tables automatically for certain types of queries, as needed.

A heap table refers to a table created with the MEMORY storage engine. MySQL supports several storage engines that store data in different ways. The MEMORY storage engine does not persist any data to disk; it holds data in RAM. If the MySQL server stops, all data in RAM is lost. There are also size limits for a table using this storage engine; it can’t be larger than the max_heap_table_size option, in bytes. These tables are useful for high-performance, short-term access to small datasets. It often makes sense to use the MEMORY storage engine for temporary tables.

Views do not store data at all. MySQL views are not “materialized” views like in some other RDBMS products. Every time you query the view, you are really running the underlying query in the view definition. Although some uses of views may automatically cause a temporary table to be created, just as some queries do.