I have a queue table where entries are added by a service with the WAITING status and a due date, which may be seconds to weeks from now. Those due entries are consumed and updated with the PENDING status while being processing by another service. Then, when processing is done, they are updated again with a final status. After that they aren't touched ever again, and are needed only for reporting that's generated from a slave replica.
During our daily operations we may insert from 5M to 10M new entries in that table, but that's supposed to increase a lot soon and I'd like to improve db I/O and application performance before it becomes a serious problem.
The table is too big to fit in memory or even the SSD we have available, so I'm considering moving the hot entries to a separate identical table.
Is it a good idea to do that with partitioning, using the status id as key and let MySQL manage the moving for me, keeping the hot partition on the SSD, and even small enough to fit into memory? For instance, keeping all rows with status id less or equal than X on the "active" partition, and greater than X on the "history" partitions? Can I use columns partitioning, so I can keep all rows with status less or equal than X on the hot partition, but still further partition the "history" by id for easier storage?
