Efficiently implementing an orders table?
Multiple tables and Partitioning would have similar performance -- DELETE from one place (table or partition) and INSERT into another.

The syntax for partitioning -- if you PARTITION BY LIST(status) -- is a simple UPDATE ... SET status = '...' WHERE ...;.  But the cost is effectively delete+insert.

But... What is the benefit?  Do you usually do SELECTs that say WHERE status = 'active'?  (or FROM ActiveOrders if using multi-table)?  If so, the performance is probably the same.

But, but... With a single table, INDEX(status, etc) works even slightly better for finding SELECT ... WHERE status = 'active' and etc;

Bottom line:  There are pros and cons of each approach.  Some of the pros and cons show up in the schema, some in the SQL you need to write.

Recommendation:


Start with a single table.
Six months from now, re-open the question.  By then, you will have several schema changes to make and you will be itching to rewrite the SQL.  At that time, do some experimenting with the 3 options (keep single table, use multiple tables, and use partitioning).


It should be possible to write a small number of SQL statements to mass-change from the old schema to the new.