Difference between heap table and temporary table in MySQL?
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
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.