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?