Is this approach a good or bad idea to increase MySQL performace?

I am building an ecommerce CMS, in the admin section I want to display the total number of orders made, total earnings from sa开发者_运维知识库id orders, and also display the totals from each category individually.

I can do this by querying various tables and counting up the sums, but would this be a better way:

Instead I am thinking of having a table that looks like this (and has just one record):

id | total_orders | total_earnings | toy_cat_sales | apparel_cat_sales | etc...
--------------------------------------------------------------------------------
1  | 10           | 10034          | 4             | 6                 | etc...

Now every time a purchase is made I can have this record be updated, for example when a new toy order is made I can update the toy_cat_sales column and also the total_orders and total_earnings columns to reflect the new purchase.

During the actual looking up process, querying this one table and just displaying it's values would obvisly be much faster than performing counts and calculations on multiple tables with possibly tens of thousands of records.

But is this approach worth it overall? I do know that there will be just one admin and many customers. Orders will take place a lot more too as compared to the admin checking these stats in the back end. So the updates will take place a lot more than the lookups on the admin side if the 2nd approach is implemented.

I'm no MySQL expert so what would the experts here do, and I will just go with that.

---

This is probably not a good idea.

While it will make some queries simpler/faster, it is extra work on every insert. Not to mention the new programming requirement of keeping this updated. You seem to realize that this is not worth it in your last paragraph when you acknowledge 'Orders will take place a lot more'

This single-row table solution is not very extensible either. Every category needs its own COLUMN, something that requires altering a table.

I think your best solution is simply writing a query to find these stats when needed. At most a view. Either of these can be made to include a join with your categories table, making it far more extensible for the addition (or removal) of categories.

Even if you decide that keeping this metadata is worthwhile (which it most likely is not), there are better ways to do it than this table.

---

The problem you run into with this approach is that you have to add a new column to that table every time a new product is carried. You really don't want to do that.

You'd be much better off with something along the lines of a products table where each row is a product, an orders table, and an orders_products table that has an fk of the order and product to tie each product from a specific order to that order. Then just tally everything up when needed. The time required for a computer to do that math is nothing compared to the time you'll waste with maintenance with the idea above, especially once you start adding new columns to that table and it has millions of rows in it, which takes some time.