MySQL temporary vs memory table in stored procedures

Question

What's is better to use in a stored procedure: a temporary table or a memory table?

The table is used to stored summary data for reports.

Are there any trade offs that developers should be aware off?

CREATE TEMPORARY TABLE t (avg (double));

or

CREATE TABLE t (avg (double)) ENGINE=MEMORY;

Answer 1

I Just wanted to point out that, in 2021 using MariaDB-10.3.27, the code @biziclop said doesn't work, is not the case any more, this is possible:

CREATE TEMPORARY TABLE tmp1 AS
SELECT * FROM products LIMIT 10;
SELECT * FROM tmp1, tmp1 AS t2;

(I just tested it)

Answer 2

Why is this restricted to just the two options? You can do:

CREATE TEMPORARY TABLE t (avg double) ENGINE=MEMORY;

Which works, although I'm not sure how to check if the memory engine is actually being used here.

Answer 3

A temporary table will only exist for the duration of your session. A table declared with Engine=Memory will persist across user sessions / connections but will only exist in the lifetime of the MySQL instance. So if MySQL gets restarted the table goes away.