We have a table that acts as a queue of items to process. This table has a status column that can contain 0 or 1 to indicate if the record is still active. The table currently has ~4MM rows and will grow quickly.
The distribution of the rows are approximately 5% with status = 0 and 95% with status = 1. Typical queries look only for records with status = 0.
As the table grows, the queries are starting to slow down. This is because the MySQL optimizer isn't using an index on the status column because its cardinality is too low.
We are considering partitioning the table into 2 partitions by the status column. The thought is that we can make use of partition pruning so typically only 5% of the total records needs to be analyzed. We keep the status=1 records just for archival reasons.
My question is whether this approach will have the desired affect I am looking for or will the negatives outweigh the benefits? Will updating rows from status=0 to status=1 cause problems with performance?
If the performance of updating the status column is a concern, what about just create a separate table to store the archived records only, then schedule the moving of your archived records from the original table to this archive table every certain period during server's quiet time?
This way after the first migration you'll end up with much smaller "active" table and the I/O slowness of changing the status from '0' to '1' is not realized by the end user.