Q: additional fields in existing mysql table OR separate table/s I have been looking around stack for advice and im still not 100% sure about the best way to improve this. I have a mysql INNODB 'product' table that stores about 130K records. There are about 80 fields for misc product data etc and we have been then adding supplier stock data fields x 3 for each supplier (cost/id/stock) resulting in another 35-40 extra fields.
We run supplier data feeds into separate tables per supplier via loaddata OR php scripting for xml/xls feeds. We then run a single query to update the cost/stock in products with the latest data in the relevant supplier table matching the ids in each table. This process then runs for each of our supplier feeds (about 15 at the moment) in some case once daily in others twice/3/4 times feed sizes vary from a few hundred to 1/3/20/30K.
We then run a script (several times a day due to differing import schedules of supplier data feeds) that compares stock at all suppliers (from the main product table data) and generates a price based on the cheapest supplier with stock at that point. This then updates the overall best product price in the product table for each item with stock somewhere.
The update best price script selects all records from the table that has some stock somewhere and does its calculations before then updating each product individually with the price. The issue we have is that we get slow downs during this time which can sometimes only be a minute or 2 but can sometimes seem to take 5/6 up to 10 minutes depending on site traffic etc presumably because the product tabe is being updated and re-indexed whilst this is going on for up to 20/30 thousand records on each run.
The product table is the busiest table for the site and when the updates a running we see the cpu go up to 300/350%. We are trying to work out whether our best option initially is to separate the stored best price and aggregate stock values into another table and to join this table when we are pulling out the product data to get round the indexing/locking issues OR whether we simply need to migrate to a new server that can still handle the db/webserver/email etc OR to run a dedicated DB server.
The problem re migration or new dedicated db server is the cost which is the stopping point for the boss when the server copes fine at 10/20/30/40% CPU outsde of these slow update periods. If we were to go for a db server the easiest option is to get a new server and run the db from that to save reinstalling our custom apps/mail server/website application files etc etc. If we do that would we be able to get away with a lower spec server that on one we currently have OR do we really need to think about the db server being the better spec on of the 2?? Any help or general advice would be greatly appreciated!!! Thanks.

A: It sounds like your product table is denormalized.  If you're not quite sure what that means, please read up on it.  Understanding that is critical to your success.
If I were you, I'd choose this option: "separate the stored best price and aggregate stock values into another table and to join this table when we are pulling out the product data to get round the indexing/locking issues".  That is, I believe you should switch from a denormalized to a normalized schema for your product table and the associated supplier information.  
But, switching will take developer labor.  Usually developer labor is more expensive than new server hardware. It's not hard to migrate a mySQL database instance from one server machine to another.  If you do buy a new server machine, buy the fastest disk storage subsystems you can afford. If you use RAID go with RAID 1-0 (striped and mirrored) not RAID 5. Other than that, if you fit out the new server with 4GB of memory and a dual or quad processor, you should be fine. 
In other words, buy a 2012 run-of-the-mill midrange server machine with a good disk storage subsystem. Put your mySQL on the new machine (leave the other stuff alone).
But, think through how much your application needs to scale up.
Is this product table already approximately the size it will be a few years from now? Or is your company planning to add lots of new products and new vendors in the months and years to come? If you have to scale this system up, you really must choose the first option (use developer labor and change to a better, normalized, DBMS schema).   If you bite the bullet now and get this change made, your system will scale up much more easily.
But, if your company's product line is fairly static, adding a new server for five or ten thousand bucks or so is the cheapest way to go.
