What's the point of the cataloginventory_stock_status_idx table?

Question

I have tried really hard to understand the cataloginventory_stock relations - I do know that both the stock_item as well as stock_status tables get updated when a stock item gets modified, but I wasn't able to figure out whether the stock_status_idx table is really required.

From what I can see, the stock_status_idx table contains the same information as the stock_status table. Is it a temporary table only? I did not see any problems with wrong stock status if I manually updated the stock_item and stock_status tables, but did not update the stock_status_idx table.

The thing is.. I thought it's somehow used for caching/the indexer. However, even if I didn't modify the stock_status_idx table, the stock status displayed just fine in the backend and in the frontend.

So, what's the point of the stock_status_idx table?

Thanks so much for your help.

Answer 1

I was wondering the same question and decided to dig as I could not find an answer anywhere. It seems like the _tmp and _idx are used as temp holders for your indexed data.

For example, you can look at the reindexAll() method in app/code/core/Mage/Catalog/Model/Resource/Category/Indexer/Product.php and you will understand that it is using _idx tables to temporarily store it's data when generating the indexes:

$this->useIdxTable(true);
...
$idxTable = $this->getIdxTable();
....
$query = $select->insertFromSelect($idxTable, ...

At the end of the same method you will notice a nice $this->syncData() method call. You probably guessed it! The function lives in app/code/core/Mage/Index/Model/Resource/Abstract.php and is doing just that, syncs the _idx with main:

$this->_getWriteAdapter()->delete($this->getMainTable());
$this->insertFromTable($this->getIdxTable(), $this->getMainTable(), false);
$this->commit();

Best of luck!

Answer 2

idx table is used only when Magento need select many products based on their stock item, so the index on these tables are faster than use the main inventory tables.

I recommend you to write your data onto this table too. Or better, use Magento API or Magento App over PHP to write into Magento Database, is much more safe.