I have a system where users are submitting work to my server frequently. With 1500 active at one time, it's about 6000 units of work being submitted per minute. I've been using one large table to summarize the work, and then when a solution gets run, the database clears out old work to keep the size manageable.
My goal is to split the table into two sections: A work counter, and a 15 minute log.
The work counter table would run an UPDATE with each work submission (primary key is worker_id, solution_id), incrementing the third column by 1 each time work is submitted. All the update commands will use the worker_id and solution_id as the WHERE clause, so it will always be hitting a primary key pair.
The log table would be storing 3 columns: worker_id (INT), submit_time (timestamp), valid (ENUM ('Y','N')). Every minute, the table would have a query which removes information older than 15 minutes.
My current plan is using InnoDB for the counter table, and a MEMORY table for the log. Would these be the correct choices of engines for the purpose of each table? The timestamp log is not critical, so if the table got lost due to a restart, it would not be a problem.