How can I fix this scaling issue with soft deleting items?

Question

I have a database where most tables have a delete flag for the tables. So the system soft deletes items (so they are no longer accessible unless by admins for example)

What worries me is in a few years, when the tables are much larger, is that the overall speed of the system is going to be reduced.

What can I do to counteract effects like that.

• Do I index the delete field?
• Do I move the deleted data to an identical delete table and back when undeleted?
• Do I spread out the data over a few MySQL servers over time? (based on growth)

I'd appreciate any and all suggestions or stories.

UPDATE:

So partitioning seems to be the key to this. But wouldn't partitioning just create two "tables", one with the deleted items and one without the deleted items.

So over time the deleted partition will grow large and the occasional fetches from it will be slow (and slower over time)

Would the speed difference be something I should worry about? Since I fetch most (if not all) data by some key value (some are searches but they can be slow for this setup)

Answer 1

I'd partition the table on the DELETE flag.

The deleted rows will be physically kept in other place, but from SQL's point of view the table remains the same.