Mysql – Scaling out MySQL & Redundancy-Speed tradeoff

database-designMySQL

I'm building an e-commerce service for a group of sellers. They have a common HQ who manufactures their product.

Tables:

  1. order (id, seller_id, timestamp)
  2. order_products (order_id, product_id, seller_id, timestamp, pincode)
  3. transaction (id, seller_id, timestamp)
  4. transaction_products (transaction_id, product_id, seller_id, timestamp, pincode)
  5. seller (id, pincode, name)
  6. product(id, price)

Specifications:

  1. There are 100 sellers
  2. Each vendor performs 500 transactions per day
  3. Each transaction has 4 products associated with it
  4. Each Vendor places two orders per day to HQ
  5. Each order have 50 products

HQ Requirements:

  1. How many products were sold by which seller in a given month
  2. How many products were sold in a given pincode in a given month
  3. Orders placed by all sellers in a given month

Seller Requirements:

  1. View cost of order placed by him/her (the seller)
  2. View his/her sales of a given month

The product is ready and application works just fine. But, I'm concerned with the two things.

  1. Scaling: Being really new, I don't know much about scaling out or sharding or clustering. How much time have I got until I can keep these aside?
  2. Redundancy: As you can see in transaction_product & order_product, I've reused columns from transaction & order, respectively. The redundant columns are: timestamp, seller_id, pincode. My idea was to avoid joins. But I'm not sure if joins would be more expensive than current redundancy. Can anyone point me in the current direction?

Best Answer

As you grow, keep an eye on the slowlog -- it will help you find the queries that need better indexes and/or reformulation.

No sharding for those small specs.

Clustering (eg, Galera or Innodb Cluster) give a high level of HA, plus some scaling.

Do not fear JOINs. On the other hand, don't "over-normalize".

If the monthly reports run to slowly, "Summary tables" can fix that.

No PARTITIONing unless you plan to purge "old" data.

Related Question