Optimising OpenCart: From 4 Seconds to 400 ms
Contents
Article
- Enable the MySQL/MariaDB slow query log and use pt-query-digest — the usual culprits are oc_product, oc_product_description and oc_category_path.
- Add composite indexes on the fields you filter by most, e.g. (status, date_available).
- Nginx FastCGI cache drops TTFB on cached pages from 2–4 s to 10–50 ms.
- Redis as the cache driver speeds up dynamic pages by 30–50%; PHP 8 OPcache + JIT adds 10–20%.
Result on a real 80k-product store: category TTFB 4,200 → 380 ms, MySQL CPU 240% → 18%.
A slow OpenCart is one of the most common problems store owners run into. This walkthrough covers the process from analysis to result.
Step 1: read the slow query log
Start by enabling the slow query log in MySQL/MariaDB:
slow_query_log = 1 slow_query_log_file = /var/log/mysql/slow.log long_query_time = 0.5
Use pt-query-digest to find the heaviest queries. The usual culprits are oc_product, oc_product_description and oc_category_path.
Step 2: indexes and queries
Add composite indexes on the fields you filter by most. A classic case: category page queries doing a full table scan because there is no index on (status, date_available).
Step 3: FastCGI cache
Nginx FastCGI cache gives the biggest single win on static pages. TTFB drops from 2–4 seconds to 10–50 ms on cached pages. Configure exclusions for the cart and the admin panel.
Step 4: Redis object cache
OpenCart supports Redis through its standard cache driver. Moving session and object cache off disk and into Redis speeds up dynamic pages by 30–50%.
Step 5: OPcache and JIT
PHP 8.x JIT compilation adds 10–20% on CPU-heavy work. Set opcache.memory_consumption=256 and opcache.jit_buffer_size=64M.
The result
After a full pass on a real 80k-product store: category TTFB from 4,200 ms down to 380 ms, MySQL CPU from 240% down to 18%.
Frequently asked questions
Where should OpenCart optimisation start?
With the slow query log in MySQL/MariaDB (long_query_time = 0.5) and pt-query-digest to find the heaviest queries. Most often they hit oc_product, oc_product_description and oc_category_path.
Which indexes help the most?
Composite indexes on the fields you filter by. A classic case is category page queries doing a full table scan because there is no index on (status, date_available).
What gives the biggest single speed-up?
Nginx FastCGI cache on pages that can be cached: TTFB falls from 2–4 seconds to 10–50 ms. Keep the cart and the admin panel out of the cache.
Is Redis worth it for OpenCart?
Yes. OpenCart supports Redis through its standard cache driver, and moving session and object cache off disk speeds up dynamic pages by 30–50%.
