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.