What are the differences between Heap table and temporary table in MySQL?

  • Duration: Heap tables are stored in memory. Therefore a Heap table remains in existence even if the session is disconnected. When we restart Database, Heap tables get cleaned up.
  • Temporary tables are valid only during a session. Once the session is disconnected, temporary table is cleaned up.
  • Privilege: We need special privilege to create a Temporary table. Heap tables are just another form of storage in MySQL.
  • Sharing: Temporary tables are not shared between clients. Each connection will have a unique temporary table. But Heap tables can be shared between clients.