Why is OpenCart search slow?

The cause

A leading wildcard cannot use an index

OpenCart splits the search box into words and matches each one with a leading-and-trailing wildcard against the product description table. A pattern that begins with `%` cannot use a B-tree index, so MySQL reads the rows — every product, in every language you have installed, on every search.

Tick “Search in product descriptions” and it adds the same scan across the description column, which is the largest text in your catalogue.

-- both majors, simplified
WHERE pd.name LIKE '%running%'
  AND pd.name LIKE '%shoes%'
   OR pd.description LIKE '%running shoes%'   -- when the box is ticked

What does not fix it

Bigger hosting and page caches

A faster server scans the same rows a little faster, and search still competes with page loads and checkout for the same PHP workers and the same database connection pool. It buys margin, not a different shape.

Page caches do not help either: cached pages are ones many people request, and every search term is close to unique. Search is the opposite of a cacheable page.

What does

Move the query off the shop

NitroSearch keeps a copy of your catalogue in a dedicated search engine and answers queries straight from the shopper’s browser. Your server is never in the search path, so search adds no load to your hosting at all — and the result comes back in around a tenth of a second regardless of how busy the shop is.