Partitioning a table vs moving entries to another table I don't understand how partitioning helps me more or less than using a separate table to store older records.

I need to maintain a list of stock entries along with historical records about the stock.

So here I have the option that if some stock is discontinued, I can move that stock to another table that will be accessed only at the time of reports when a user wants to see old data. 

Another option I have is partitioning the table, but as I don't know much about partitioning I am not sure if this will really help me.

Stock records will be accessed each time stock is removed from inventory.  Once the remaining quantity reaches 0, the item needs to remain in the table for reporting purposes only.  A new entry is created upon purchase of further stock.

I have gone through the dev.mysql documentation on Partitioning.

Questions


Can anyone shade some light on this ?
Is this the right way of doing this ?
Should we handle it differently ?
Partitioning is helpful with large tables and management purposes for your DBA.  If you have 50GB of data you should start looking into partitioning just for perfomrance alone, as Kim Tripp stated.  

However, as a DBA I find partitioning great for management of large tables and reporting data that comes from a OLTP source, something which I think you are describing.  Partitioning will allow you to also create different indexes, different fill factors, and utilize indexed views on top of them to gather massive performance boosts for aggregation type functions utilizng "Indexed Views over Partitioned Tables".

In my recommendation, get familiar with partitioning.  It can save you disasters.  I once inhereted a table that was 6 billion rows and growing at 2-3GB a day with only 20GB free on the drive.  I was able to create a new partition for new data, copy out all the old data to different databases on RAID 5, and update the code to refer to views; all without client downtime in a 24/7 shop.  Partitioning is essential as data sets grow larger.