Tech Stack: I am using MySQL 8 with InnoDB engine. Application is built using Java with Spring & Hibernate. Database is hosted in AWS RDS and web apps in EC2.

I have two primary tables for handling orders:

  • Orders (Rows Count = 1,294,361)
  • Orders_Item (Rows Count = 2,028,424)

On peak days, my storefront generates orders at a rate of 30 orders per minute and each order information is written primarily in above mentioned tables.

I have a separate project for OMS (Order Manager System) which lookup in same tables to provide me list of pending orders, changing their status, fulfillment etc. With this order generation rate, it usually causes slowdown of OMS order list page. Also I have a CMS (Customer Management System) which also lookup in same tables for handling customer queries related to their orders.

Hence, these two tables are used at a very high rate that causes slowdown on one or other place. I am using best possible indexes in these tables.

I am thinking of below mentioned solution:

  • Maintain duplicate orders data, one will serve for CMS and new order creation as a master and duplicate will server for OMS

But I am not sure is this a right approach. Please share your inputs.

  • 2
    Could you confirm your write rate? 30 orders per minute = 1 order every 2 seconds. Assuming you have the proper indexes on your tables, that shouldn't cause any problems at all. Commented Jan 5, 2021 at 6:38


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Browse other questions tagged or ask your own question.