0

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?

2
  • Have you looked at partitioning tables? I think that gets around the worst of the minuses of manually splitting your single logical table into multiple physical tables. You'd want to pay special attention to the rules for partitioning functions. I know you could partition date from different calendar years into different tables; I'm less certain how things work if the value you'd want to partition on changes after the creation of the record (like, partitioning on order status, and moving records when the status changes to one that should be in the other table).
    – RDFozz
    Apr 21, 2017 at 1:15
  • Do queries have Date filters along with Status Filters? If it is yes then Single table with Partition will help you lot. It would helpful if you give the definition of ACTIVE_ORDERS / OLD_ORDERS.
    – SQL.RK
    Apr 21, 2017 at 12:07

1 Answer 1

0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.