Ask HN: Need some help in database design issue

Just thought to ask it here in case someone has already gone thru this issue before.

I am going to have a catalog of about half a million to one million products.

On a day to day basis there will be updates to this products, maybe their description or rating will change.

New products will added everyday.

I need to show things on my website like most popular product, product which is getting more popular (some sort of trending etc.)

Now my doubt is how to store all this information in the database.

I can store the product information in a table in MySQL. When the daily batch update happens, append the new rows to the table. I need to keep the old rows as I need the historical price and rating information.

From this master table run jobs which will take some time to create statistics like most popular products etc.

The website runs against this summary table.

I am using Python and Pylons for the frontend. I will use celery for creating the jobs.

I also need to provide full text search on the product catalog which I think will need to go against master database which might have a million rows so will be quite slow. Is there any other way for it?

So is there a good way to store this kind of historical information in the DB and create trend based reports, full text search for some catalog that gets updated on a daily basis?