A filter panel that takes four seconds to respond is perceived as broken. The problem is rarely the module itself: it comes from how the queries are built and from what the database has to scan to answer them.
Here is how to locate the cause before changing anything.
Measure before assuming
Three readings, in this order.
The actual response time of a filtering request, measured in the browser’s network tab on your biggest category with two or three active filters. Write the value down: it is your starting point.
The share of that time spent in the database. Enable PrestaShop’s profiling in a test environment: it shows the number of queries executed and their cumulative duration. If database time represents 80% of the total, no point looking elsewhere.
The number of queries executed. It is often the revelation: a filtered category page that executes three hundred queries has a design problem, not a server power problem.
This third point deserves attention. A multiplication of queries generally signals a counter calculation value by value, or products loaded one by one in a loop.
The four main causes
Joins on the attribute tables. Filtering on three attributes means joining the value tables several times. On a catalog of twenty thousand combinations, these joins produce considerable intermediate sets.
Counter calculation. Displaying the number of results behind each value means an aggregation per value. On a panel with sixty values, that can mean sixty aggregations at every selection change.
The total count for pagination. Counting the rows of a wide selection costs almost as much as reading them.
Missing indexes. On the link tables between products, attributes and categories, a missing index turns a lookup into a full table scan.
Diagnosing the indexes
It is the most profitable and fastest check.
Retrieve the slowest filtering query from your database’s slow query log, then run it preceded by the execution plan keyword.
Three signals to spot in the result.
An access type indicating a full scan on a large table. It is the sign of a missing or unusable index.
A number of examined rows out of all proportion with the number of returned rows. Examining two hundred thousand rows to return forty indicates that filtering happens after reading rather than through an index.
The mention of a temporary sort or a temporary table, which signals that the database cannot satisfy the sort through an index and must materialise an intermediate result.
A practical point: useful indexes on these tables often cover combined columns, and their order matters. An index on product then attribute does not serve the same queries as an index on attribute then product.
AJAX Faceted Filters for PrestaShopThe faceted navigation that filters fast and indexes right€99.00
Reduce the work rather than speed it up
Before technical optimisation, three functional decisions often have more effect.
Reduce the number of filters. Every filterable attribute adds potential joins and counters to calculate. Twelve filters of which four are used cost three times too much.
Give up counters on large catalogs, or calculate them only on the most used filters. The lost comfort is real, so is the time saved.
Limit the combination depth. Beyond three simultaneous filters, selections become rare and expensive. You can cap without anyone noticing.
These three measures require no development and can be tested in an afternoon.
The dedicated index
When the previous measures are not enough, the structural answer consists of no longer querying the catalog tables at filtering time.
The principle: a flat, pre-calculated table containing for each product its filterable values, its price, its availability and its categories. Filtering becomes a simple query on a single indexed table.
Three implementation points.
The update must trigger on every product, price or stock change. A desynchronised index shows products that no longer exist or hides new arrivals.
The complete rebuild must remain possible, and its execution time measured: on a large catalog, it can take several minutes and must not run at peak hours.
Bulk triggering. A catalog import modifying ten thousand products must not trigger ten thousand unit rebuilds. Plan a deferred mode.
The cache, and its limits
Caching is useful and it is often misused on this subject.
Caching filtering results works well when the requested combinations are few and repeated. That is the case on most stores: a few dozen combinations cover most of the traffic.
Two limits to know. The cache does not handle the first visit of each combination, which stays slow. And it must be invalidated on every stock or price change, otherwise you display false information.
On availability data, the cache duration must stay short, a few minutes, which strongly reduces its interest. A common approach is to cache the list of matching products and fetch price and stock live.
The infrastructure-side checks
Three points that are not about code.
The memory allocated to the database. A database that cannot keep its indexes in memory rereads them from disk on every query. It is the most frequent cause of generalised slowness after catalog growth.
The query cache and buffer configuration, to adjust to the real size of your data rather than staying on the installation defaults.
Competing resources. A catalog export or a scheduled task running at the same time as the traffic peak degrades everything. Shift what can be shifted.
The complete approach
Six steps, in this order of decreasing profitability.
1. Measure the response time and the number of queries.
2. Check the indexes with an execution plan on the slowest query.
3. Reduce the filters offered to only those used.
4. Disable or limit counters if the catalog is large.
5. Set up a cache on the frequent combinations.
6. Consider a dedicated index if the first five steps are not enough.
A point of method: measure after each step. Taking several steps at once deprives you of knowing which one produced the effect, and thus of knowing where to concentrate the effort next time.
The AJAX Faceted Filters module for PrestaShop integrates these mechanisms on PrestaShop 8 and 9: pre-calculated dedicated index with incremental update, result counters in a single aggregation, per-combination cache with invalidation on stock and price changes, and configurable limitation of the combination depth.
