Skip to content
Tutorials Center
My learning

Topics

Lesson 19 of 27 ยท Shipping and maintenance

Performance in Extensions

Keep OpenCart extensions fast: count queries, add indexes, use the cache class, avoid N+1 in product lists, limit event cost and call remote services with timeouts.

A store owner rarely notices one slow extension while testing on a development machine with ten products. They notice it six months later, when the catalog has 40,000 products, a category page takes four seconds and nobody can tell which of the 30 installed extensions is to blame. Performance problems in extensions are almost always the same few mistakes: too many database queries, queries without an index, work repeated on every request that could be cached, and slow calls to other servers made while the customer waits.

This lesson gives you a way to find those mistakes before your customers do. Real OpenCart code (controllers, models, events) is read-only here because OpenCart cannot run on this site. Every idea also has a plain-PHP stand-in on SQLite that you can run, and each section says what is real and what is the stand-in. If you have not yet read how models and the database object work, see Models, the Registry and the Database first.

Measure first: count the queries

The cheapest performance metric for an OpenCart page is the number of database queries. A product list that issues 3 queries is healthy. One that issues 150 will be slow even on a fast server, because each query has a round trip to MySQL. You do not need a profiler to see this: wrap the database object, count calls, and print the number while you develop.

In a real store you can enable the database query log in your own development copy by wrapping $this->db->query() in a small class that increments a counter, or use the MySQL general log on a development database (never on production). The stand-in below does the counting with a wrapper around PDO.

The N+1 problem in product lists

OpenCart's own getProducts() model method returns a list of products. A very common extension bug is to loop over that list and run a query per product to fetch extra data: a badge, a supplier name, a custom field, a special price. With 20 products per page that is 1 query for the list plus 20, or 40 if you need two extra values. That shape is called N+1 (one list query, N follow-up queries).

The fix is to ask for all the extra data in one query, either with a JOIN or with a single WHERE product_id IN (...) query, and then match rows to products in PHP.

flowchart LR
    subgraph Slow["N+1: 1 + N queries"]
      L1["List query"] --> A1["Query for product 1"]
      L1 --> A2["Query for product 2"]
      L1 --> A3["... one per product"]
    end
    subgraph Fast["One JOIN or IN list"]
      L2["Single query with<br/>all extra data"]
    end

The slow shape grows with the page size; the fast shape stays at one query.

PHP
<?php
// READ-ONLY: the slow pattern inside a catalog model (OpenCart 4 style).
public function getBadgesSlow(array $products): array {
    foreach ($products as &$product) {
        // one query PER product: this is the N in N+1
        $query = $this->db->query("SELECT `label` FROM `" . DB_PREFIX . "acme_badge`
            WHERE `product_id` = '" . (int)$product['product_id'] . "'");
        $product['badge'] = $query->row['label'] ?? '';
    }
    return $products;
}
PHP
<?php
// READ-ONLY: the fixed pattern: one query for the whole page.
public function getBadgesFast(array $products): array {
    $ids = array_map(fn($p) => (int)$p['product_id'], $products);
    if (!$ids) {
        return $products;
    }

    $query = $this->db->query("SELECT `product_id`, `label` FROM `" . DB_PREFIX . "acme_badge`
        WHERE `product_id` IN (" . implode(',', $ids) . ")");

    $labels = [];
    foreach ($query->rows as $row) {
        $labels[(int)$row['product_id']] = $row['label'];
    }
    foreach ($products as &$product) {
        $product['badge'] = $labels[(int)$product['product_id']] ?? '';
    }
    return $products;
}

Notice that the ids are cast to int before they are joined into the SQL string. That is the OpenCart habit for numeric values; see Security for Extension Developers for why.

PHP Counting queries: N+1 versus one JOIN

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with fgets(STDIN)), it gets the next line from this box.

The slow path needed 25 queries for 12 products and the fast path needs one, with identical output. On a real page with 100 products per page (some stores raise the limit) the difference is 201 queries against 1.

When an extension must run once per product, ask: "Can this be a JOIN or an IN list?" If the data lives in another system, fetch it for the whole page in one call, not one call per product.

Indexes: make your tables searchable

If your extension creates its own table (see Install, Uninstall and Permissions), add an index for every column you filter or join on. Without one, MySQL reads every row of the table to answer WHERE product_id = 42. A table with 5 rows does not care. A table with 2 million rows does.

SQL
-- READ-ONLY: creating a table with the right keys in an install() method
CREATE TABLE IF NOT EXISTS `oc_acme_badge` (
  `badge_id` INT(11) NOT NULL AUTO_INCREMENT,
  `product_id` INT(11) NOT NULL,
  `label` VARCHAR(64) NOT NULL,
  `date_end` DATE NULL,
  PRIMARY KEY (`badge_id`),
  KEY `product_id` (`product_id`),
  KEY `date_end` (`date_end`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

In MySQL you check whether a query uses an index with EXPLAIN SELECT ...; the key column shows the index used and rows shows how many rows MySQL expects to read. SQLite has a similar tool, so the stand-in can show the same idea. The table prefix oc_ above is the default DB_PREFIX; in code you always write DB_PREFIX . 'acme_badge'.

PHP An index changes the query plan

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with fgets(STDIN)), it gets the next line from this box.

Use the cache class for repeated work

OpenCart has a cache object in the registry ($this->cache) with get(), set() and delete() methods. The default driver stores files under the storage folder, and you can configure other drivers. The pattern is always the same: build a key, try get(), and compute and set() only on a miss.

PHP
<?php
// READ-ONLY: cache a computed badge list per language and store
$key = 'acme_badges.' . (int)$this->config->get('config_language_id') . '.' . (int)$this->config->get('config_store_id');

$badges = $this->cache->get($key);

if (!$badges) {
    $badges = $this->model_extension_acme_module_badge->getActiveBadges();
    $this->cache->set($key, $badges);
}

Three rules keep cached data correct:

  1. Put everything that changes the result into the key. Language, store, currency and customer group are the usual suspects. A key without the language shows French badges to English visitors.
  2. Never cache per-customer data under a shared key. Prices for a logged-in customer group, cart contents and anything private must not be shared.
  3. Delete the key when the source changes. When the admin saves a badge, call $this->cache->delete('acme_badges') (check how your OpenCart version matches keys by prefix) so shoppers see the update.

The cache has a default lifetime, so stale data fixes itself eventually, but "eventually" is not a promise to a merchant who just edited a price.

PHP A cache with expiry and a safe key

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with fgets(STDIN)), it gets the next line from this box.

Events and templates: where hidden cost lives

An event handler runs on every request that matches its trigger. A handler on catalog/view/product/thumb/after runs once per product card, so 20 cards means 20 calls. If each call makes a query, you have rebuilt the N+1 problem inside an event. Keep handlers cheap: load the data once per request (store it in a property of your event class or in the cache) and only match it to the current product inside the handler.

Twig templates are compiled and cached under the storage folder, so a template costs time only the first time it is rendered. That is why the developer settings let you turn the theme cache off during development. Turn the cache back on before you measure, otherwise you measure the compiler, and remember that customers on a production store see the cached version. See Twig Views and Language Files for the template side.

Remote calls must have timeouts

Calling a courier, tax service or payment API while the customer waits ties your page to someone else's uptime. Always set both a connect timeout and a total timeout, keep them short (two or three seconds for a page that a customer is waiting on), and decide what happens on failure: show a default, hide the feature, or queue the work for later.

PHP
<?php
// READ-ONLY: a remote call with timeouts and a safe fallback
$curl = curl_init('https://rates.example.com/v1/quote');
curl_setopt_array($curl, [
    CURLOPT_RETURNTRANSFER => true,
    CURLOPT_CONNECTTIMEOUT => 2,   // seconds to connect
    CURLOPT_TIMEOUT        => 3,   // seconds in total
]);
$body = curl_exec($curl);
$failed = $body === false || curl_getinfo($curl, CURLINFO_HTTP_CODE) !== 200;
curl_close($curl);

if ($failed) {
    // use a cached or flat rate, and log the failure
    $this->log->write('Acme rates: remote call failed, using fallback');
    $quote = $this->getFlatRate();
}

Combine this with the cache from earlier: cache a successful answer for a few minutes so ten shoppers with the same cart do not make ten remote calls. For work that does not affect the page at all, use a queue as in Project: Order Webhook Exporter.

A short performance checklist

  • Count queries on list pages and cart pages with 20+ products.
  • Replace per-product queries with one JOIN or one IN (...).
  • Index every column in a WHERE, JOIN or ORDER BY of your own tables.
  • Cache with keys that include language, store and currency, and delete on change.
  • Keep event handlers cheap; they run for every matching call.
  • Give every remote call timeouts and a fallback.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. A catalog page shows 24 products and your extension runs one query per product. How many queries does the extension add, and what is this called?

    One query per product adds N queries on top of the list query, which is the N+1 pattern. Fetch the data for all products at once with a JOIN or an IN list.
  2. 2. Which of these must be part of a cache key for a list of translated badges?

    The key must contain everything that changes the result. Without the language id, shoppers in one language can receive text cached for another.
  3. 3. Why should your own extension table have an index on product_id when you look rows up by it?

    An index lets the database jump to matching rows instead of scanning the whole table, which matters as the table grows.
  4. 4. A courier API is sometimes slow. What is the safest design for a shipping quote the customer waits for?

    Timeouts keep the page responsive, and a fallback keeps checkout working when the remote service is down.

Summary

  • Measure first: the number of queries per page is the easiest signal.
  • Fix N+1 by fetching extra data for the whole page in one JOIN or IN query.
  • Add indexes on columns your own tables filter and join on.
  • Use $this->cache with keys that include language and store, and delete keys when data changes.
  • Event handlers run for every matching call, so keep them cheap.
  • Remote calls need timeouts and a fallback, or should move to a queue.

Next: Packaging, Marketplace and Upgrades