Im trying to set up a Magento Multistore (1.9) with 300k Products distributed over one website with ~30 stores. Each store has his own root category (10k products).
Its not possible for me to reindex my products. Reindex fails after several hours with a Mysql Error "The total number of locks exceeds the lock table size"
Anyway while debugging I noticed that when rebuilding the product flat index, a database table for each store is being written. So far so good BUT each product flat db table (i.e catalog_product_flat_4) contains ALL 300k products.
Thats what I dont understand. I thought if you had a different root category for each store only the products of that category had to be indexed for each store.
Expectation:
catalog_product_flat_4 = 10k rows
catalog_product_flat_5 = 10k rows and so on
Reality:
catalog_product_flat_4 = 300k rows
catalog_product_flat_5 = 300k rows and so on
What is wrong with my expectation?
// edit
One little example to maybe make my issue more clear: I want to sell shoes and printer. I want to have to different stores, one that has all my shoes and one with all printers. I want to manage Customers,Orders etc in one place. In the printer store/store_view customers should not be able to see/buy printers and vice versa.
To archieve that im using one Magento instance with one website and two stores with two different root categories.
Why on earth would the product flat table for the store with the shoes have also the printers from the other store?
