Why PrestaShop Search Slows Down As Your Catalog Grows

The symptom

With a demo catalog of twenty or thirty products, PrestaShop search always performs well, whatever configuration you choose, the page answers in a fraction of a second. The problem only shows up once the catalog is large for real, and by then it is too late to notice it early: a shop with 50,000, 100,000 or 200,000 products can end up with a search results page that takes several seconds to answer, or a filtered category page that becomes the slowest page on the whole site.

This is not a feeling. On a synthetic catalog of 200,000 products, using PrestaShop's native search engine (the one that runs inside the shop's own database, with no external engine), a search results page took 28 seconds and a large category page 30 seconds before a focused round of optimisation work. Afterwards, the same page takes between 2 and 6 seconds. Still seconds, not milliseconds, and that gap is the subject of this article.

Why time grows faster than the catalog does

The most common cause is not "the database is slow", it is that the same work gets redone several times within a single request. A faceted search engine has to answer two different questions on every page: which products to show, and for every available filter, how many products would match if the shopper picked it. If the set of products matching the current search is recomputed from scratch for every filter, instead of being computed once and reused, the cost stops being proportional to the number of products and becomes proportional to the number of products multiplied by the number of filters. On a small catalog you never notice. On 200,000 products with dozens of active filters, a search that in theory touches five products can end up scanning the whole catalog a dozen times over.

The same thing happens with smaller pieces of work repeated an enormous number of times: if the price of every product on a category page is fetched from the database one at a time instead of in bulk, a hundred products per page can easily turn into a hundred thousand queries on a large catalog with per-group or per-quantity pricing rules. None of these queries is slow on its own. It is the sheer count of them that becomes unbearable.

The hidden cost of the numbers next to filters

There is a second, separate cost: showing how many products match each filter value, the classic "Red (12)" next to a checkbox. With an external search engine (Elasticsearch, OpenSearch, Meilisearch) that number comes for free, as part of the same query that computes the results. With the native engine, which works inside the shop's own database, every single filter value can require its own counting query.

On a 200,000-product catalog with around 2,250 attribute and feature groups, turning off exact filter counts (showing the list of values without the number next to them) took a category page with one active filter from 43.3 seconds to 11.9 seconds. On the feature and attribute facets alone, the time dropped from 6.8 and 5.4 seconds respectively to 15 and 19 milliseconds. That is not a rounding error, it is the difference between a query that has to count rows one by one in the database and one that just reads a list of distinct values up to a limit, without counting all of them.

For a shop owner the practical question is simple: is that number in parentheses worth its cost? On a catalog of a few thousand products, almost certainly yes. On a catalog of hundreds of thousands of products with many features, it can be the single most expensive part of the whole page, and turning it off is often a configuration checkbox, not a rewrite.

What a real test on that size of catalog shows

On a synthetic catalog of 200,321 products, an internal test of the full search page (results plus all facets) measured response times between 2.2 and 2.4 seconds. That is consistent with the 2 to 6 second range quoted above, measured on a different setup: two independent measurements telling the same story, a catalog of that size answers in a time measured in seconds, not tenths of a second, even after a good round of optimisation. Anyone claiming otherwise for a catalog that size on the database-only native engine alone is probably measuring on a smaller catalog than they say.

What to check before you find out in production

• Run at least one test with a product count close to your real one, not the demo catalog. Problems of this kind are invisible under ten thousand rows and very visible above it.
• Check your hosting's PHP memory limit. Many shared plans still sit at 128 or 256 MB, and operations that load database rows into memory instead of letting the database do the counting can exceed that on large catalogs with many features or attributes imported from CSV.
• If you use the native engine on a large catalog, check whether you can turn off exact filter counts, or whether it is worth moving to an external engine (Elasticsearch, OpenSearch or Meilisearch) that computes them as a by-product of the same query.
• Whichever search module you are evaluating, ask whether its cost was actually measured on a large catalog, with a number attached. "Fast" without a number next to it does not say much.

The short version

PrestaShop search does not slow down because of some mysterious flaw, it slows down because work gets repeated more times than necessary, and that only becomes visible once the catalog grows large enough to expose it. The two easiest levers to check are whether the same result set gets reused across filters, and whether the numbers next to filters are computed cheaply or expensively. We spent months measuring and fixing exactly this class of problem inside cll_essearch, our search and faceted navigation module for PrestaShop, which works with the native engine as well as Elasticsearch, OpenSearch and Meilisearch. If you recognise the symptoms in this article on your own shop, this is precisely the kind of problem it was built to solve.

Elasticsearch Search & Faceted NavigationLightning-fast, typo-tolerant search and modern modal faceted navigation with the built-in engine, or powered…€99.99View product