Performance Tuning Your PrestaShop Store: From Database Queries to Full Page Cache

Most of the slow PrestaShop shops we've been called in to rescue had the same underlying story: a homepage taking three to five seconds to first byte, an "Add to cart" button that hangs, a category page that pegs one MySQL core at 100% while the rest of the box sits idle. And in nearly every case the win came from the same place — not the CDN, not another image compressor, not a sixth caching module bolted on top, but the database and the queries running against it. That is what this guide is about: the path from "my store feels slow" to a tuned database, in the order that actually works, ending where a full page cache finally earns its keep.

This is the database-and-query chapter of PrestaShop performance, and it owns that scope deliberately. If you're still at the "is it even slow, and where?" stage, start with how to check whether your store is slow and what to do about it. If you want the wider map of every cause — modules, hosting, the lot — read what actually makes PrestaShop slow. This post assumes you've already pointed the finger at the database and want to fix it properly.

Why the database is usually the bottleneck

A normal PrestaShop page fires somewhere between 80 and 300 SQL queries. On a 10,000-product shop with layered navigation enabled, a single category page can sail past 500. Most of those queries finish in one to three milliseconds and you never notice them. The problem is the two or three that don't — the ones eating 150 to 400ms each. Those are what blow out your time-to-first-byte, and no amount of front-end optimisation touches them, because they run before PrestaShop has rendered a single byte of HTML.

The "so what" for a store owner is blunt: a slow database means every customer waits on the page that decides whether they buy. Google's own research ties slower load times to lower conversion; the exact figure varies by study and by store, so treat it as directional, not a promise. What we can say from auditing shops every week is that the customer who waits four seconds for a product page is measurably likelier to leave than the one who waits one. The database is where that second usually hides.

Step 1: Turn on the slow query log before you change anything

Before touching a single setting, find out what is actually slow. Guessing is how shops end up with five caching modules and the same slow query underneath all of them. The slow query log records every query that crosses a time threshold you set — it is the single best diagnostic tool MySQL gives you, and it costs nothing.

Drop this into your MySQL or MariaDB config (usually /etc/mysql/mysql.conf.d/mysqld.cnf or /etc/mysql/mariadb.conf.d/50-server.cnf):

# Enable slow query logging
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow-query.log
long_query_time = 0.5
log_queries_not_using_indexes = 1
min_examined_row_count = 100

The three settings that matter:

• long_query_time = 0.5 — log anything over 500ms. The default of 10 seconds is useless for e-commerce; by the time a query takes 10 seconds the customer has already bounced.
• log_queries_not_using_indexes = 1 — catches full table scans that are still fast today because the table is small. These are the queries that take the shop down six months from now when the table has grown fifty-fold.
• min_examined_row_count = 100 — keeps the log focused on queries that do real work, not trivial ones that happen to drift over the threshold under momentary load.

Restart MySQL and let it run for at least a full 24 hours, so you cover quiet hours, peak hours, and any cron jobs. Then summarise the worst offenders:

# Quick summary of the slowest queries by total time
mysqldumpslow -s t -t 20 /var/log/mysql/slow-query.log

# Or use pt-query-digest from Percona Toolkit for deeper analysis
pt-query-digest /var/log/mysql/slow-query.log > /tmp/query-report.txt

pt-query-digest from the Percona Toolkit is what we reach for. It clusters similar queries, ranks them by total time spent across the whole period, and tells you in two minutes which five queries are responsible for most of your database load. It's free and in every distro's package manager. Install it.

Step 2: Read the EXPLAIN plan — the skill that separates guessing from knowing

Once the log has named your offenders, the next question is why. Put EXPLAIN in front of the query and MySQL tells you how it plans to retrieve rows: which index it picked, how many rows it expects to read, whether it has to sort on disk.

EXPLAIN SELECT p.id_product, pl.name, p.price
FROM ps_product p
LEFT JOIN ps_product_lang pl ON p.id_product = pl.id_product AND pl.id_lang = 1
LEFT JOIN ps_category_product cp ON p.id_product = cp.id_product
WHERE cp.id_category = 42 AND p.active = 1
ORDER BY p.date_add DESC;

What to read in the output, and what to fear:

What you want is type: ref or type: eq_ref, a named index in key, and a small rows figure. When you see type: ALL together with Using temporary; Using filesort, that single query is doing the maximum amount of work for the minimum amount of result.

A real PrestaShop example: the specific-price lookup

The query we see in slow logs more often than any other is the specific-price lookup. Quantity discounts, group prices and date-bound promotions all live in ps_specific_price, and on shops that have run flash sales for years it accumulates fast:

EXPLAIN SELECT * FROM ps_specific_price
WHERE id_product = 1542
AND id_shop IN (0, 1)
AND id_currency IN (0, 1)
AND id_country IN (0, 8)
AND id_group IN (0, 1, 3)
AND id_customer = 0
AND from_quantity <= 1
AND (`from` = '0000-00-00 00:00:00' OR `from` <= NOW())
AND (`to` = '0000-00-00 00:00:00' OR `to` >= NOW());

The from_quantity <= 1 clause matches rows whose threshold is at or below the requested cart quantity (here 1), and PrestaShop then resolves the single best applicable specific price by priority and order. On one client shop with around 200,000 rows in ps_specific_price, this query was examining most of the table to return three rows. A composite index sorted it out:

ALTER TABLE ps_specific_price
ADD INDEX idx_product_shop_currency
(id_product, id_shop, id_currency, id_country);

Query time collapsed from hundreds of milliseconds to under a millisecond. Multiply that by 36 products on a category page and the saving is the difference between a usable shop and one customers complain about. This is the whole loop in miniature: the log names the query, EXPLAIN shows why it's slow, an index fixes it. Everything else in this guide is variations on that theme.

Step 3: Index strategy for module tables

Core PrestaShop tables ship with reasonable indexes. Module tables almost never do. We've reviewed hundreds of third-party modules and the share that bothers to index its custom tables properly is depressingly low — and your shop pays for it on every page load, because those tables join into the queries that build category and product pages.

Four principles cover most cases:

• Index columns used in WHERE clauses. If the module queries WHERE id_product = X AND id_shop = Y, you want a composite index on (id_product, id_shop).
• Column order matters in composite indexes. Most selective column first — a column with 10,000 unique values belongs before one with three.
• Cover your ORDER BY. If you sort by date_add DESC on every page, include date_add in the index. That's how you eliminate the filesort you spotted in EXPLAIN.
• Don't over-index. Every index slows down INSERT and UPDATE. On a write-heavy logging table an extra index can cost more than it saves.

A well-indexed module table for the classic "show approved reviews for a product, newest first" pattern looks like this:

CREATE TABLE ps_mymodule_reviews (
  id_review INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  id_product INT UNSIGNED NOT NULL,
  id_customer INT UNSIGNED NOT NULL,
  id_shop INT UNSIGNED NOT NULL DEFAULT 1,
  rating TINYINT UNSIGNED NOT NULL,
  status TINYINT NOT NULL DEFAULT 0,
  date_add DATETIME NOT NULL,
  INDEX idx_product_status (id_product, status, date_add),
  INDEX idx_customer (id_customer),
  INDEX idx_shop_status (id_shop, status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

To find existing tables that are missing indexes, ask the schema directly:

-- Module tables over 1,000 rows with no secondary index
SELECT t.table_name, t.table_rows
FROM information_schema.tables t
LEFT JOIN information_schema.statistics s
  ON t.table_name = s.table_name
  AND t.table_schema = s.table_schema
  AND s.index_name != 'PRIMARY'
WHERE t.table_schema = 'prestashop'
AND t.table_name LIKE 'ps_%'
AND t.table_rows > 1000
AND s.index_name IS NULL
ORDER BY t.table_rows DESC;

Any table over 1,000 rows with no secondary index is a candidate. Match that list against your slow query log and you'll find the cause of half your worst queries in one sitting.

Step 4: PrestaShop query patterns that bite at scale

Beyond generic tuning, PrestaShop has a handful of query shapes that keep showing up as the cause of trouble once a catalogue grows. These are the ones we hunt for first when auditing a shop.

The layered-navigation count problem

Layered navigation fires a COUNT(DISTINCT) for every filter facet. On a category with 15 attribute groups and 200 possible values, that's 200-plus count queries per page load. Each one is cheap on its own:

SELECT COUNT(DISTINCT p.id_product)
FROM ps_product p
INNER JOIN ps_product_attribute_combination pac ...
WHERE ... AND pac.id_attribute = 47;

But 200 of them at 10ms each is two seconds of pure database time before a single product card renders. Two fixes work in practice: a pre-computed facet-count table, or simply trimming the visible filters in the category configuration so you're not asking the database to count facets nobody clicks. The second costs nothing and helps more often than store owners expect.

The N+1 query in module list hooks

A module registered on a product-list hook that runs one query per product gives you the classic N+1: 36 products in a category means 36 extra queries, per module. Three modules doing this and you've added 100-plus queries to every category page. The pattern to recognise — and the batch rewrite that fixes it:

// Bad: one query per product, inside a list hook
public function hookDisplayProductListReviews($params) {
    $id_product = (int)$params['product']['id_product'];
    $row = Db::getInstance()->getRow(
        'SELECT AVG(rating) AS avg_rating
         FROM ps_mymodule_reviews
         WHERE id_product = ' . $id_product
    );
}

// Good: one query for the whole list, cached for the display hook.
// actionProductSearchProviderRunQueryAfter fires once after the listing
// query, with the full result set in hand.
public function hookActionProductSearchProviderRunQueryAfter($params) {
    $products = $params['result']->getProducts();
    $ids = array_column($products, 'id_product');
    $ratings = Db::getInstance()->executeS(
        'SELECT id_product, AVG(rating) AS avg_rating
         FROM ps_mymodule_reviews
         WHERE id_product IN (' . implode(',', array_map('intval', $ids)) . ')
         GROUP BY id_product'
    );
    // store in a static cache the display hook reads
}

If you're not sure which of your modules does this, the slow query log will show the same query shape repeating dozens of times per page — that's your N+1 fingerprint.

Cart-rule evaluation

Every active cart rule is evaluated against every cart, on every page load that touches the cart. Hundreds of live rules — and we've inherited shops with more than a thousand — turn every cart and checkout page into a join-fest. If you've got more than 50 active rules:

• Archive anything expired — set active = 0 on rules whose date_to is already in the past. PrestaShop date-filters applicable cart rules by date_from/date_to, so expired rules aren't applied, but disabling and cleaning them keeps the candidate set small and cuts admin clutter.
• Merge overlapping rules wherever the business logic allows.
• Index ps_cart_rule on (active, date_from, date_to) if it isn't already.

Step 5: Tune the InnoDB buffer pool

InnoDB's defaults are written for "any database on any hardware." On a dedicated e-commerce server they're embarrassingly conservative — we've seen a 16GB VPS running PrestaShop with the buffer pool stuck at the default 128MB, the disk thrashing constantly because nothing fit in RAM. One config change usually moves the needle more than anything else on this list after the indexes are in.

The buffer pool is where InnoDB caches data pages and indexes in memory. Anything served from it comes out of RAM; anything that isn't has to go to disk — on a busy product page, the difference between a 50ms query and a 500ms one. The headline settings:

[mysqld]
# Buffer pool: the single most important setting.
# 70-80% of RAM on a dedicated DB server; 50% (min 1GB) on shared hosting.
innodb_buffer_pool_size = 4G
innodb_buffer_pool_instances = 4

# Larger log files = fewer disk writes. 25% of buffer pool, max 2G each.
innodb_log_file_size = 1G

# 2 = flush to OS buffer each commit, disk write once/sec — a good compromise
# for e-commerce (1 = full ACID/safest, 0 = fastest/riskiest).
innodb_flush_log_at_trx_commit = 2
innodb_flush_method = O_DIRECT

# I/O capacity by disk type. SSD: 2000-4000. HDD: 200-400. Cloud SSD: 1000-2000.
innodb_io_capacity = 2000
innodb_io_capacity_max = 4000

# Per-table tablespace (default in MySQL 5.7+, verify it's on)
innodb_file_per_table = 1

Size the buffer pool against your real data, then check the hit ratio:

-- Total database size
SELECT ROUND(SUM(data_length + index_length) / 1024 / 1024 / 1024, 2) AS total_gb
FROM information_schema.tables
WHERE table_schema = 'prestashop';

-- Buffer pool hit ratio (you want > 99%)
SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_read_requests';
SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_reads';
-- hit_ratio = (1 - reads / read_requests) * 100; below 99%, raise the pool

A published benchmark from Releem's testing on MariaDB 10.5 showed proper InnoDB tuning (a 3.2GB buffer pool for a 1GB database) cut response time from 610ms to 370ms and raised queries-per-second by half. That matches what we see ourselves — but the size of the win depends entirely on how badly the buffer pool was undersized to start with, so don't expect a fixed number, measure your own.

One trap: query cache on MySQL 8 vs MariaDB

MySQL 8.0 removed the query cache entirely — Oracle decided the mutex contention it caused under concurrency cost more than it saved. MariaDB kept it, and on read-heavy PrestaShop shops it can still help:

# MariaDB 10.5+ only — these variables do not exist on MySQL 8
query_cache_type = 1
query_cache_size = 64M
query_cache_limit = 2M

On MySQL 8, don't go looking for it. Spend that effort on a properly sized buffer pool and application-level caching instead. If you're on MySQL 8 and not married to it, MariaDB 10.11 runs PrestaShop perfectly well and most hosts will switch you on request. Which engine and how much RAM you get is partly a hosting question — separating the parts of a host that matter from the marketing is its own topic, covered in choosing hosting for PrestaShop.

Step 6: Keep the data lean — bloat is a query problem

A tuned query still slows down if it's scanning a table that has quietly grown to tens of millions of rows. PrestaShop logs visitor connections, page views, emails, 404s and dead carts more or less forever, and the back office never tells you. We've inherited shops with 50 million rows in ps_connections_page that nobody had ever cleaned — every analytics query crawling through years of junk.

This guide won't repeat the full pruning playbook, because it's a sibling topic in its own right: which tables bloat, how often to clean them, and how to automate it lives in database cleanup: why your store gets slower over time. The one thing to carry from there into this work: after a large DELETE, the space isn't returned to the OS until you OPTIMIZE TABLE, and a fragmented table keeps your freshly-built indexes slower than they should be. Prune, optimise, then re-check EXPLAIN.

Step 7: Layer caching — but in the right order

Only now does caching belong in the conversation. Caching on top of slow queries doesn't fix the queries — it hides them, and makes things worse the moment the cache expires and a thundering herd hits MySQL at once, which is exactly when you can't afford it. With the database lean and indexed, two cache layers pay back, in this order.

Application cache (Redis) first. Once the queries are fast, moving the right caches off the filesystem and into RAM takes another solid chunk off TTFB — but be clear about which cache you're moving, because these are three separate things. An object/cache backend (Redis or Memcached) speeds up the repeated cacheable lookups that core and modules route through PrestaShop's cache API. PHP sessions are a separate concern: storing them in RAM needs its own session-handler configuration. And Smarty's compiled templates and template cache are governed by Smarty's own settings and filesystem paths, not by the object-cache backend — switching to Redis doesn't stop Smarty regenerating those files on a cache clear. The actual setup — enabling Smarty cache, CCC and the legacy cache backend from Back Office > Advanced Parameters > Performance (PrestaShop stores these as configuration values such as PS_CACHE_ENABLED / PS_CACHING_SYSTEM, and which backends are available depends on your PrestaShop version and any cache module you've installed) — and the gains to expect, is in Redis cache for PrestaShop: setup and performance gains, and the case for why most stores need it in Redis for PrestaShop.

Full page cache last. PrestaShop core ships no full page cache, so this gear comes from a dedicated module or a reverse proxy — Varnish or nginx FastCGI cache — serving a fully-rendered page from memory for anonymous traffic, where PrestaShop rendering fresh is hundreds of milliseconds. How the cache behaves — invalidation when prices, stock, cart rules or orders change, how carts and logins are handled, and whether the page shell is cached with ESI/JS holes punched for the cart widget and login status — depends entirely on the module or proxy configuration you choose. The hybrid hole-punching approach is the one our own Performance Revolution module takes on mypresta.rocks itself. Which FPC approach and which modules actually do what they claim is its own comparison — see PrestaShop full page cache modules explained.

The priority order: maximum impact, minimum risk

If you take one thing from this, it's the sequence. Each step relies on the one before it, and skipping ahead is how shops end up debugging cache-invalidation bugs that wouldn't exist if step one had been done:

1. Turn on the slow query log, fix the top ten. Free, low-risk, the biggest immediate win.
2. Read EXPLAIN, add the missing indexes. Especially on module tables — reviews, points, custom fields, anything with its own join table.
3. Tame the PrestaShop-specific patterns. Layered-navigation counts, N+1 hooks, runaway cart rules.
4. Tune the InnoDB buffer pool. One config change, restart, done.
5. Prune and optimise bloated tables. So the indexes you just built stay fast.
6. Add Redis, then a full page cache. Last, and only on a database that's already fast.

If you don't know where to start, start with the slow query log — 24 hours of real data tells you more than any benchmarking module ever will. And if the log throws up a query you don't recognise, that database analysis is one of the things we do every week; the module catalogue includes Performance Revolution, which runs the cache layers above on this very site. The shops that go fast and stay fast are the ones that fixed the database first and reached for caching last.