I found this out on a specific store based on OpenCart 3. The catalog contains around 20,000 products, and the store runs in 6 language versions. At the same time, the products are relatively simple – without complex variants, filters, options, or attributes.
Despite a powerful server, a category page could take as long as 8–10 seconds to load.
Interestingly, the store was also using Redis, but in this particular case it brought practically no noticeable improvement.
The problem turned out to be much simpler.
OpenCart 3 and a Large Product Catalog
The number of 20,000 products alone should not be a problem for a properly configured OpenCart installation and a MySQL/MariaDB database.
Of course, six language versions significantly increase the number of records. For product descriptions alone, in simplified terms, we may have:
20,000 products × 6 languages = around 120,000 product descriptions
On top of that, there are:
-
product-to-category assignments,
-
SEO URLs,
-
store assignments,
-
prices,
-
promotions,
-
manufacturer data,
-
images,
-
categories and their translations.
Even so, a database of this size should not cause a standard category page to take 10 seconds to generate.
In my case, a dump of the entire database was around 256 MB, so we are definitely not talking about a database size that should in itself be a problem for powerful hosting.
Redis Does Not Always Solve Performance Problems
One of the first things that may come to mind with a slow store is cache.
The store was already running Redis.
In theory, it should reduce the number of operations performed while generating pages and reduce system load. In practice, however, the difference was small.
Why?
Because Redis will not fix poorly chosen application logic.
If OpenCart performs a series of expensive SQL queries while generating a page, and their results are not effectively cached or depend on the current category contents, even a very fast cache system will not eliminate the actual source of the problem.
So you can have:
-
a fast server,
-
SSD/NVMe,
-
plenty of RAM,
-
Redis,
-
PHP 8.x,
and still wait several or even a dozen seconds for a page to be generated.
The Culprit: Product Count in Categories
The problem was found in the OpenCart 3 settings.
In the administration panel, there is an option:
System → Settings → Options → Products → Category Product Count
If this option is enabled, OpenCart may calculate the number of products assigned to individual categories while generating the category tree.
The result is information such as:
T-Shirts (1250)
Trousers (834)
Hoodies (542)
Jackets (391)
In a small store, the cost of such calculations is practically unnoticeable.
With 20,000 products, many categories, and 6 language versions, the situation looks completely different.
OpenCart has to repeatedly execute queries that count products. Depending on the menu structure and the number of categories, this may mean many calls to the function responsible for determining the number of products matching specific conditions.
The query itself may use, among other things:
COUNT(DISTINCT p.product_id)
in combination with product tables, product description tables, store assignments, and category assignments.
A single such query does not necessarily have to be particularly slow.
The problem starts when it is executed repeatedly during a single request.
The Effect After Disabling the Counter
Disabling one option:
Category Product Count → No
produced an immediate effect.
The category page generation time dropped from around:
8–10 seconds
to around:
2 seconds.
In practice, the store became several times faster after disabling a function that, from the customer's point of view, was not even particularly important.
To be honest, I was already about to move the online store to a VPS, but something told me to dig around here and there a little more.
Why Do Six Languages Make the Situation Worse?
A multilingual OpenCart installation stores separate descriptive data for each language.
For products, one of the tables used is:
product_description
If a store has six languages, a single product may have six description records.
With 20,000 products, we therefore get around 120,000 records in this table alone.
OpenCart queries must also take the current:
language_id
into account.
The mere fact of having six languages should not cause performance problems, but it increases the amount of data the database has to work with.
If repeated COUNT() queries are added on top of that, the cost starts to become noticeable.
Is It Worth Adding MySQL Indexes Right Away?
Indexes can make a huge difference in large OpenCart stores.
It is worth checking indexes especially in tables such as:
product
product_description
product_to_category
product_to_store
category_description
seo_url
However, this does not mean that you should immediately add dozens of custom indexes.
First, it is worth determining which query is actually slow.
In my case, the database and a lack of appropriate indexes were also the first suspects. However, it turned out that simply disabling the unnecessary product counting mechanism produced a much greater effect.
Only if the store still runs slowly is it worth analyzing queries using:
EXPLAIN
and enabling the slow query log in MySQL or MariaDB.
You Do Not Always Need to Optimize OpenCart Code
OpenCart 3 also has several areas that can be optimized programmatically.
One example is retrieving the product list. The standard model first retrieves product IDs and may then perform additional operations for subsequent items related to fetching detailed data.
With large catalogs, it is worth taking a closer look at such mechanisms.
However, there is one important rule:
if the store starts running quickly after finding a specific problem, it is not worth rebuilding half of the engine just because, theoretically, it could be optimized even further.
In this case, after disabling product counting, the store started running fast enough, so no further changes to the product model were necessary.
What Should You Check When OpenCart 3 Runs Slowly?
If you have a dozen or several dozen thousand products and OpenCart starts slowing down noticeably, do not immediately assume that you need a more powerful server.
First, check:
-
Category Product Count
Disable it and compare the page generation time.
-
Menu and Mega Menu Modules
Some templates or extensions count products for each category on their own.
-
MySQL/MariaDB Indexes
Especially in tables connecting products, categories, stores, and languages.
-
Slow Query Log
It allows you to find queries that take hundreds of milliseconds or several seconds to execute.
-
EXPLAIN
It allows you to check whether MySQL is using indexes or scanning entire tables.
-
The Number of Queries Executed During a Single Request
Sometimes the problem is not one slow query, but dozens of similar queries executed one after another.
-
Cache and Redis
They are very useful, but they will not replace optimizing poor SQL logic.
A More Powerful Server Is Not Always the Solution
This is one of the more common mistakes when diagnosing slow online stores.
If the site is slow, the first thought is:
you need to increase RAM, CPU, or move to a higher hosting package.
Meanwhile, one poorly designed query or a function executed dozens of times can easily waste the resources of even a very powerful machine.
The server then performs unnecessary work faster, but it still performs it.
In the case described here, the store was running on a high-end hosting package, was using Redis, and yet a category page still took as long as 8–10 seconds.
Disabling one function reduced this time to around 2 seconds.
Summary
A large number of products in OpenCart 3 does not have to mean a slow store.
20,000 products and 6 language versions is still a size that OpenCart can handle efficiently.
If a category page takes 8–10 seconds to load, it is worth first looking for a specific mechanism generating unnecessary SQL queries.
In my case, the culprit turned out to be:
Category Product Count.
After disabling it, the category generation time dropped from around 8–10 seconds to around 2 seconds.
Redis had practically no impact on the problem because the bottleneck was not a lack of cache, but the cost of repeatedly counting products.
This is a good example of how, when optimizing OpenCart, the biggest performance increase sometimes comes not from installing another extension or changing the server, but from finding a single function that performs far more work than necessary.