When creating a semi-temporary table for chat messages (i.e. there won't be any messages older than 60 seconds in the table, as they're deleted once a minute by an scheduled event), is there any noteworthy performance gain using HEAP/Memory instead of InnoDB?
Try this: Open two windows with MySQL client sessions.
In the first session, create a test table with MEMORY engine and give it a few rows.
mysql> use test;
mysql> create table chat (
id serial primary key,
msg text,
likes int default 0,
created_at datetime default current_timestamp
) ENGINE=MEMORY;
mysql> insert into chat set msg = 'sup';
mysql> insert into chat set msg = 'sup';
mysql> insert into chat set msg = 'sup';
In the second window, try updating a row. I throw in another expression using sleep to simulate taking some time.
mysql> use test;
mysql> update chat set likes = likes + sleep(60) where id = 1;
Again in the first window, while the second window is still doing its sleep, try reading the data:
mysql> select * from chat;
Notice it hangs!
Memory tables only support table-level locking. Your selects, inserts, updates, and periodic deletes will all queue up against each other, even though they're working on different rows.
This could make it seem like you have bad performance, because all queries to the table must be done serially. You get no concurrency.
Tables don't have performance; queries have performance. You haven't described any queries in your question. You must consider which queries you are going to run against the data, and the rate of queries, and concurrency, etc. to evaluate which storage engine is best for your app.