CODEWITHSUNDEEP
mysqldatabasedatabase-design
user275157
user275157

Reputation: 1352

mysql multiple table/multiple schema performance

A quick bit of background - We have a table "orders" which has about 10k records written into it per day. This is the most queried table in the database. To keep the table small we plan to move the records written into it a week or so ago into a different table. This will be done by an automated job. While we understand it would make sense to pop the history off to a separate server we currently just have a single DB server.

The orders table is in databaseA. Following are the approaches we are considering:

  1. Create a new schema databaseB and create an orders table that contains the history?
  2. Create a table ordershistory in databaseA.

It would be great if we could get pointers as to which design would give a better performance?

EDIT:

Better performance for

  1. Querying the current orders - since its not weighed down by the past data
  2. Querying the history

Upvotes: 0

Views: 1072

Answers (2)

Branko Dimitrijevic
Branko Dimitrijevic

Reputation: 52137

You could either:

  • Have a separate archival table, possibly in other database. This can potentially compilcate querying.
  • Use partitioning.

I'm not sure how effective the MySQL partitioning is. For alternatives, you may take a look at PostgreSQL partitioning. Most commercial databases support it too.

Upvotes: 2

Nick Maroulis
Nick Maroulis

Reputation: 487

I take it from your question that you only want to deal with current orders.

In the past, have used 3 tables on busy sites

new orders, processing orders, filled orders,

and a main orders table

orders

All these tables have a relation to the orders table and a primary key.

eg new_orders_id, orders_id processing_orders_id, orders_id ....

using a left join to find new and processing orders should be relatively efficient

Upvotes: 1

Related Questions