Level 54
You're probably being overly paranoid about performance. If you have indexes on the fields that you'll need to use for various querying then you shouldn't have to worry too much about slowdowns..
1 like
Sep 4, 2015
4
Level 8
Needing help with app/mysql architecture.
I'm developing an Ecommerce platform that is a little unique in that it allows customers to modify their order for a period of time. Basically, a customer has an ordering window (sometimes as long as 6 weeks) where they can add/update/items on their order, and they can only have one order open at a time. All orders would be saved to the DB initially, there would never be a shopping cart in a session. My concern is that if my orders table starts to grow to say a million+ records, that the shopping experience could start to slow down over time, as customers would be pulling their current order from this table. I thought a possible solution was to have an archived orders table and move any orders over there once the order was finalized and no longer modifiable by the customer. The problem with this approach is that it made it difficult to run reports across all orders, as I would have to combine results from both tables.
I'm looking for some advice on how best to approach this, am I being overly paranoid about mySQL performance? Is it a good idea to separate orders into separate open and archived tables? If so, what is the best way to run reports across 2 tables with Laravel? Or should I look into saving date used for reporting in another way? Perhaps using a different DB like Mongo?
Thanks!
Level 8
Thanks for your reply. On the customer end (which is where I'm most concerned about performance) the customer would be accessing the record by the unique ID, which I'm assuming would be very fast, even with million of records. My question then would be, if the customer is making updates to these records, let's say changing the quantity of an item, would I need to index the quantity field, or does that not matter?
Level 11
Indexes are used for improving speed on select queries and where clauses but can slow down insert and update queries so you wouldn't need to put a index on the quantity for improving the customer queries.
As for your original post, I agree with @willvincent, I would say don't try to prematurely optimise your app. That being said it's still obviously a good idea to keep growth in mind e.g. if it's possible that you're going to be changing you db/reporting solution then maybe look at using a repository pattern.
I wouldn't worry about having a separate table to hold historic data; it might turn out that it actually makes a lot of sense to do it further down the line but until then I wouldn't worry about it.
With the reports, it would completely depend on how in-depth you were going to make them, how often they're going to be used and what type of reporting you're going to build. Generally, I would just add a simple caching layer around the areas that are going to be slower and/or chunk the data.
1 like
Level 8
Thanks for your answers. I think I will just keep everything in one table, and if things start to slow down I can always delete records that are over so many years old. This probably won't be an issue for a long time anyway. I was just concerned with building any kind of bottleneck into my app, but I think I probably am just being too paranoid.
Please or to participate in this conversation.