Elasticsearch and Search Across More Than 280,000 Products

An e-commerce store with more than 280,000 products in its catalogue lives or dies by its search. Categories help people find their bearings, but anyone who has a part number, a toner model or a paper size in mind types it into the search box and expects the right product at the top, immediately. If the result arrives late or is wrong, the visitor does not browse: they leave.

On Initpc, the e-commerce store for office supplies, stationery, IT equipment and school products that we have supported as technical partner since 2006, search was for a long time the point where the platform showed its limits first. WooCommerce's native engine, designed for stores of ordinary size, had become slow and imprecise at this scale and — less visible but more serious — a burden on the database that slowed down everything else on the site. This deep dive from the Initpc case study explains how we moved it to Elasticsearch and why we are already building the next step.

Why WooCommerce's native search collapses on a large catalogue

WordPress and WooCommerce search is not a search engine: it is an SQL query. When a visitor types "black cartridges", the site queries the posts table with a LIKE '%cartridges%' condition on title and content and joins it with the metadata table to filter by price, availability and attributes. Every WooCommerce product is a post with many linked metadata rows: every search is a join between a large table and an enormous one. With more than 280,000 products, several things happen at once.

Slowness. A LIKE with a leading wildcard cannot use indexes: the database scans every row, every time.

Irrelevant results. SQL answers "contains the word" or not. It does not know that a title matching in full is worth more than a description where the word appears by chance, it does not understand plurals, it does not forgive a typo. The right product exists, but it ends up at the bottom of the list.

Database load. This is the hidden damage. The same MariaDB that answers searches also serves product pages, cart, checkout and the ERP synchronisations. Every heavy search ties up the database for everyone: during a traffic peak, the search box slowed down even the customers who were paying.

The decision: take search out of the database

Optimising the native queries had limited headroom: the problem was not a badly written query, but a data model born for a blog and adapted to a store.

The choice was Elasticsearch, installed in Docker on a VM dedicated to the search role inside the client's Proxmox infrastructure, with ElasticPress as the bridge to WooCommerce. These are the same reasons that guide the rest of the e-commerce infrastructure architecture: every role on its own machine, open technologies, everything under our control.

Elasticsearch is a full-text engine: instead of scanning rows it consults an inverted index, a map that for every term already says which documents it appears in and with what weight, like the index at the back of a book. ElasticPress intercepts WooCommerce searches, rewrites them in Elasticsearch's language and returns to WordPress the identifiers of the products to display.

	WooCommerce native search	Elasticsearch with ElasticPress
Where it runs	On the same database as the store (MariaDB)	On a separate service in Docker, on a dedicated VM
How it searches	LIKE on text and joins on metadata, on every request	Ready-made inverted index, with linguistic analysis
Ordering	By date or alphabetically, no notion of relevance	Relevance score by field, weight and match
Typos and variants	No tolerance	Fuzziness, synonyms and plurals handled by the analyser
With more than 280,000 products	Rising cost per search, database tied up for everyone	Stable cost per search, database free for orders and pages

Initial indexing and continuous synchronisation

The initial indexing read the entire catalogue — title, description, product code, categories, attributes, price, availability, brand — and built a document in the index for every product. With more than 280,000 products this is not an operation to launch from the admin panel: we ran it in batches from the command line, during low-traffic hours, because it is the source database that does the heavy lifting.

The part that really matters is what happens afterwards. The proprietary ERP that imports price lists and availability from multiple suppliers and warehouses updates prices and stock levels continuously. Every time it modifies a product on WooCommerce, ElasticPress intercepts the save and rewrites the corresponding document in the index: anyone searching a moment later sees the new price and no longer finds the item that has just sold out, with no overnight cycle to wait for.

When a new price list touches a very large number of products in one go, the writes to Elasticsearch are grouped and sent in bulk, so the import does not turn into a storm of requests; a scheduled full reindex realigns any documents that may have been left behind.

Autosuggest: search starts while you type

The search box on initpc.it suggests products while the visitor is still typing. Every character sends a request directly to Elasticsearch, through a dedicated endpoint exposed on the site's domain, without going through the full WordPress cycle: no theme to load, no database query, just the engine responding.

In a catalogue where many searches start from a partial code or a model number, seeing the product appear after a few letters confirms that the item exists before you even press Enter.

Relevance: fields, weights, synonyms and typos

A full-text engine assigns every product a score and sorts accordingly. The configuration work lies in deciding what pushes that score up.

Fields are not all worth the same. A match in the title counts more than one in the description; a match in the product code counts more than anything else, because someone typing a code knows exactly what they want. We assigned different weights to title, code, brand, category and description, so the right product surfaces even when the search term appears in a great many product pages.

Words are analysed, not compared. Before entering the index, the text passes through an Italian-language analyser that reduces words to their root: "cartucce" and "cartuccia" (cartridges and cartridge) become the same thing, and capitals and accents stop mattering. A synonym dictionary, built by looking at real searches that returned nothing, covers the vocabulary of the trade: someone searching for "toner" may mean "laser cartridge", and "raccoglitore" and "registratore" are often the same lever arch file.

Typos are tolerated. Fuzzy search accepts a wrong or transposed letter and still finds the product. It is a delicate setting — too much tolerance and "penna" (pen) finds "panna" (cream), too little and every typo is a dead end — which we calibrated on real cases.

The effect on performance and on the database

The obvious benefit is search itself: fast, consistent responses. The most important result, however, is indirect. Taking searches out of the database gave MariaDB back the capacity that the most expensive queries of all were consuming: product pages, cart, checkout and the ERP's writes stopped competing for resources with the search box, and the cascading slowdowns that started from search at peak times disappeared.

The plugin's limits and the proprietary search engine in development

ElasticPress brought an enormous catalogue onto a real engine without rewriting the store, but it is a general-purpose plugin and its strength is also its limit. The structure of the index is decided by the plugin and mirrors WordPress's post-plus-metadata model; ranking can be tuned within the margins the plugin exposes, beyond which you need filters and custom code to re-check at every update; every synchronisation goes through the WordPress cycle, while the source of truth for products is the ERP.

That is why we have a proprietary search engine in development: still Elasticsearch in Docker, but with an index designed by us — schema, analysers, computed fields — and a ranking written for Initpc's catalogue, built to be fed directly by the ERP. Let us be clear about this: it is not in production. The site runs ElasticPress today; we describe it because it is the direction, not a result.

The most interesting prospect is the meeting point with Hector AI. To generate the product pages with Hector AI, the platform distils structured attributes from every item — material, use, compatibility — which today end up in the text of the product pages. Brought into the index as fields in their own right, they would make it possible to answer descriptive searches ("recycled paper for laser printers") where today only words are compared. It is a future integration, and we will build it on the proprietary engine, not on the plugin.

This work grows out of our e-commerce development and design and Docker service containerisation projects, and relies on the Hector AI platform we develop in-house.