Magento – Avoid invalidating product flat table reindexing upon product attribute value save

attributesindexingmagento-1.7

Magento 1.7.

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.

Best Answer

If you only want to update a single product attribute without running the indexer, use the following method. It will be possible to update a couple of hundred products per second that way.

First, add a new product attribute estimated_time_of_arrival.
The, in your update script:

$product->addAttributeUpdate(
    'estimated_time_of_arrival',
    $date,
    $storeId // numeric store id value, e.g. Mage::app()->getStore()->getId()
);

If it's a global scope attribute, the $storeId doesn't even matter.
If you don't care about the store ID scope, you can also use:

$product->setData('estimated_time_of_arrival', $date)
    ->getResource()
    ->saveAttribute($product, 'estimated_time_of_arrival');

Both methods are equivalent (the latter will use $product->getStoreId()if the attribute has a website or store view scope).

Of course, if you use that attribute in some way that requires indexing, you will have to reindex after the updates.

Related Topic