Efficiently implementing an orders table? Orders are typically handled with one large orders database and then status is changed as necessary "active", "fulfilled", etc. 

However, my project is scaling quite quickly so we're getting a lot of queries of the orders table and I'm not sure if this is the best way to do it as it continues to grow - as customer service systems, monitoring systems and user-facing order systems are all querying the same table. 

I'm thinking about creating an  ACTIVE_ORDERS / OLD_ORDERS table (i.e. certain systems would hit ACTIVE_ORDERS more often and others could hit OLD_ORDERS more often) and the location of these orders could be stored in a bridge table, and their interchange between tables controlled by a transaction. 

I suspect I will be making certain tasks quite difficult, for example searching through ALL (active/old) orders with customer name LIKE something might be very slow unless collated through a temporary view.

What do you think?
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.