Magento Multistore Reindex Issue

Question

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?

Answer 1

Simply because that's how Magento works, normally you would have separate websites however 300k products is up there. Hope you really know what you are trying to do, most underestimate the problems with Magento and 100ks products with hosting, indexing, product loading, international.