CODEWITHSUNDEEP
mysqlquery-optimization
ufk
ufk

Reputation: 32124

mysql optimization: current & previous orders should be on different tables, or same table with a flag column?

I want to know what's the most optimized way to work with mysql.

I have a quite large database for orders. i have another table for previous orders, whenever the order is completed, it's erased from orders and is moved to previous orders.

should i keep using this mothod or put them all in the same table and add a column that flags if it's current or previous orders?

kfir

Upvotes: 1

Views: 55

Answers (1)

Yishai Landau
Yishai Landau

Reputation: 630

In general, moving data around tables is a sensitive process - data can easily get lost or corrupted. The question is how are you querying this table - if you search and filter through it on an often basis, you want to keep the table relatively small. If you only access it to read specific lines (using a direct primary key), the size of the table is less crucial, and then I would advise to keep a flag. A third option you might want to consider, is having 3 tables - one for ongoing orders, one for historic orders, and one with the order details. That third table can be long and static, while you query the first two tables.

Lastly - at some point, you might want to move the historic data out of the table all together. Maybe you would keep a cron job that runs once a month and moves out data which is older than 6 months to a different, remote database.

Upvotes: 2

Related Questions