I noticed that when you update inventory quantities, a reindex of catalog_product_flat is not required.
That's useful, because I can set up a cron job to update inventory every night at 3 am. Importing the data for 20,000 items only takes about a minute. If reindex was required, it would add at least another 10 minutes.
Now, I want to also show Estimated Time of Arrival dates for out of stock items. I can import the data at the same time as the stock quantities. But if I create a regular attribute for that date, then every time I import, the index will be invalidated. Isn't that correct?
Is there any way I can create an attribute that works like stock quantity does, i.e. one that doesn't require any reindexing? I know there is a stock status index in 1.7, but it does not seem to get invalidated by just changing stock quantities (we use Manage Stock = No).
I was thinking that as a hack, I could use the "news_from_date" (which we don't use anyway) to hold that date.
UPDATE I changed the question Title to better reflect what my problem really is.
Fundamentally, I want to avoid the
catalog_product_flat_1 table reindex. Why? Because it drops the table and rebuilds it, and while that is happening the frontend is non-functional. With 70,000+ items to reindex (and more coming), the downtime is at least 5 minutes.
To avoid reindexing, I suggested creating an attribute that works like stock qty, but perhaps that is too hard to do.
What about saving each product and updating its index entry? I think that with UPDATE queries, the catalog_prodct_flat_1 table would continue working, and the frontend would continue working. Is that a correct assumption?
Do individual product saving methods update the index, or do they invalidate it?
As long as Magento is only sending update queries to the flat table index, I think the frontend would remain operational. A complete reindex renders the frontend unusable for some time.