How does the number of products affect magento/server performance?

I am considering deleting unused products in a magento multistore setup as I am only using 1 of the stores. I have over 25K skus but only using about 1k.

I am using magento and have read about some catalog rewrite bug which can cause some slow queries.

2 Answers 2


What it affects the most is the indexers, not only the URL rewrite index, which indeed has a glitch in CE, and disabling products does not remove them from the index. Also, price index, attribute index for layered navigation, search index, and flat article index. Incidentally, those grow about linear to product count * store view count, so you should consider deleting your unused stores as well.

Indexing performance also is affected by the number of combined products (bundle, configurable), number of categories, number of customer groups, and number of filterable product attributes.

It's not unusual for a full reindex to take several hours, so I'd definitely recommend optimizing it where possible. Also, partial reindex is affected, which means with fewer products, saving a product in the backend will be faster (if the indexers are configured as "update on save")

  • I don't know if I would call products not being removed from url rewrites upon disable as a "glitch". Just because a product is currently disabled doesn't mean you don't want it to have some SEO juice.
    – pspahn
    Mar 22, 2015 at 20:05
  • How do dead entries in a database table give you "SEO juice"? These links are going to be 404 anyways. Also, disabled products are only part of the problem, the index also contains products that are not visible individually, so for example if you have a typical fashion store with lots of configurable products and lots of categories, there is a huge amount of unused data in the URL rewrite index. Mar 22, 2015 at 22:03

I can confirm the already given answers by fschmengler and can give you only one piece of advice. Keep your database as small as possible. There is no reason the keep inactive products inside the store database. Check your queries, finetune your database. Check buffer pool size for MySQL. Keep query cache between 64 and 128MB. Check the Indexer queries. Disable unused modules via XML. Check your flat tables. Are all Attributes for Category listing inside the flat table. Are unused Attributes inside the database / flat tables? For category, listing implements some prefetching for all Products to reduce the number of database queries. Magento can easily handle 100k Skus if you keep an eye on what you are doing.

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.