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.


catalog_product_flat_4 = 10k rows

catalog_product_flat_5 = 10k rows and so on


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?

  • I don't have a solution to the problem, but I can tell you for sure that the root category has nothing to do with the products in a website. The products - websites relation is determined by what's in the websites tab on the product admin edit page. So you can have products associated to a website even if they belong in the root category of a different website.
    – Marius
    Commented Feb 26, 2015 at 15:30
  • I agree that the root category has nothing to do with the products in a website. Im talking about stores (website != stores != storeviews). When you add a store you have to select a root category so why would a store have to see the products in other roots categories associated with other stores?
    – Josh
    Commented Feb 26, 2015 at 15:53
  • One website contains stores ( that contain store views). So far so good. But the product relation is made at website level. So if a product is associated to a website it will be available in all stores and all store views that belong to that website. The category has nothing to do with the product - website relation. That's because you can have products in one or many categories, or even none. So in theory you can have a product associated to a website, not linked to any category but still be displayed in that website. (1/2)
    – Marius
    Commented Feb 26, 2015 at 16:12
  • If you want to have lighter flat tables, you should create multiple websites. One for each store your have. Then you can assign to each website only the products you need in that website (or store). (2/2)
    – Marius
    Commented Feb 26, 2015 at 16:13
  • Thanks for your answer. It seems I misunderstood this concept a little bit. Part 2 of your answer seems like it could solve my problem and reduce the Reindex time.
    – Josh
    Commented Feb 27, 2015 at 8:02

1 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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.