Online store search: from LIKE to Elasticsearch

A customer who types a phrase into a store’s search box usually knows what they want and is one step away from buying, provided they find something. In this article, which introduces our series on AI in e-commerce, we start with the basics: we review classic search methods, from LIKE in SQL, through full-text search in MySQL and PostgreSQL, custom index tables and Sphinx, to Elasticsearch, Meilisearch and SaaS services. We also suggest what to choose for which store.

What customers expect from search

Search in an online store is more than a text field. Customers type phrases the way they think and speak, not the way the product was named in the database. Good search has to handle several things at once:

• Typos and missing diacritics: “adidsa”, “wireles headphones”, or Polish words typed without Polish characters.
• Word forms: “jacket” and “jackets”, “women’s shoes” and “shoes for women”. In highly inflected languages such as Polish, a single noun can have more than a dozen forms.
• Synonyms: “laptop” and “notebook”, “hooded sweatshirt” and “hoodie”.
• Codes: SKU, EAN or the manufacturer’s part number, typed with a hyphen, without it or with a space (“XR-200”, “xr200”, “XR 200”).
• Relevance: a product with the phrase in its name should rank higher than one that mentions it in the third paragraph of the description, and an available product higher than a sold-out one.
• Search-as-you-type suggestions: in a fraction of a second, on every keystroke.
• Filters: after typing “jacket”, customers want to narrow the results by size, colour and price, ideally with the number of products next to each option.

Inflected languages raise the bar considerably. Polish has seven grammatical cases and its own diacritics, so solutions designed for English, where it is usually enough to strip an “-s” ending, work only moderately well. We look at each of the approaches below from this angle.

The simplest approach: LIKE ‘%phrase%’

This is how WordPress, and therefore WooCommerce, searches by default: each word of the phrase is searched for with the LIKE operator in the post title, excerpt and content. Simplified, the query looks like this:

SELECT p.ID, p.post_title
FROM wp_posts p
WHERE p.post_type = 'product'
  AND p.post_status = 'publish'
  AND (p.post_title LIKE '%kurtka%' OR p.post_excerpt LIKE '%kurtka%' OR p.post_content LIKE '%kurtka%')
  AND (p.post_title LIKE '%zimowa%' OR p.post_excerpt LIKE '%zimowa%' OR p.post_content LIKE '%zimowa%')
ORDER BY p.post_title LIKE '%kurtka zimowa%' DESC, p.post_date DESC;

It has one advantage, but an important one: zero configuration. The drawbacks are easy to predict:

• No index. LIKE with a % at the beginning cannot use a regular index, so the database scans all products on every search. With a few hundred products nobody will notice; with tens of thousands of products with long descriptions, they will.
• No understanding of language. In an inflected language, a phrase in one grammatical form will not find a product whose name uses another form, because neither word contains the other.
• False matches. “cat” also finds “category” and “catalogue”.
• Primitive relevance. WordPress orders results by a few simple rules (the whole phrase in the title, all words in the title, any word in the title, the phrase in the excerpt, the phrase in the content). There are no weights for categories, attributes or popularity.
• Product codes. The default WooCommerce storefront search does not search SKUs or attributes. A customer who copies a code from an invoice or a catalogue gets zero results.

LIKE has one fair use: searching by the beginning of a text (LIKE ‘XR-2%’) on an indexed column, for example the product code. There the index works and the query is fast.

FULLTEXT in MySQL and MariaDB

Since MySQL 5.6, full-text indexes work in the InnoDB engine. The database builds an inverted index (word → list of records it appears in), and a MATCH … AGAINST query returns results together with a relevance score.

ALTER TABLE products ADD FULLTEXT INDEX ft_products (name, description);

-- natural language mode: results with a relevance score
SELECT id, name, MATCH(name, description) AGAINST('winter jacket') AS score
FROM products
WHERE MATCH(name, description) AGAINST('winter jacket')
ORDER BY score DESC
LIMIT 20;

-- boolean mode: required and excluded words, prefix search
SELECT id, name
FROM products
WHERE MATCH(name, description) AGAINST('+jacket* +winter* -kids' IN BOOLEAN MODE);

It is a huge performance leap compared with LIKE, and it comes “for free” in the database the store already has. The limitations are clear, though:

• No support for inflection. MySQL cannot reduce different forms of a word to a single base form. Boolean mode with an asterisk partly helps (a prefix such as “jack*” finds “jacket” and “jackets”), but words whose stem changes when inflected, which is common in Polish, remain out of reach.
• Minimum word length. By default InnoDB indexes words of three characters or more (innodb_ft_min_token_size), so “TV” or “4K” drop out of the index. Changing this requires server configuration, which is usually not an option on shared hosting.
• Stopwords. The built-in list of words the database ignores (such as “the” and “and”) is English only.
• No typo tolerance and no synonyms.

For a store with a few thousand products this is often a sufficient and sensible solution, especially combined with a custom index table, which we describe in a moment.

PostgreSQL: full-text search and typo tolerance

PostgreSQL has a much richer built-in full-text engine: the tsvector and tsquery types, language dictionaries, weights for individual fields (from A to D) and ts_rank ranking. Some languages, including Polish, have no dictionary in the standard installation, but you can plug in an ispell dictionary (for example one based on Hunspell dictionaries). Different forms of a word are then reduced to the same base form. The pg_trgm extension adds typo tolerance (it compares words by short three-letter fragments), and unaccent lets you ignore diacritics.

In the example we use the simple configuration, which does not recognise word forms. Once a language dictionary is installed, you just swap it for your own configuration.

CREATE EXTENSION IF NOT EXISTS pg_trgm;

ALTER TABLE products ADD COLUMN search_vector tsvector
  GENERATED ALWAYS AS (
    setweight(to_tsvector('simple', coalesce(name, '')), 'A') ||
    setweight(to_tsvector('simple', coalesce(description, '')), 'B')
  ) STORED;

CREATE INDEX idx_products_search ON products USING GIN (search_vector);
CREATE INDEX idx_products_name_trgm ON products USING GIN (name gin_trgm_ops);

-- full-text search with ranking
SELECT id, name, ts_rank(search_vector, query) AS score
FROM products, websearch_to_tsquery('simple', 'winter jacket') AS query
WHERE search_vector @@ query
ORDER BY score DESC
LIMIT 20;

-- typo tolerance: "wintr jacket" still finds the product
SELECT id, name, similarity(name, 'wintr jacket') AS score
FROM products
WHERE name % 'wintr jacket'
ORDER BY score DESC
LIMIT 20;

If your store runs on PostgreSQL (like many installations of Sylius, Saleor or Medusa), this is often the best first step before reaching for a separate search engine.

A custom table with indexed data

Instead of searching the product tables live, you can prepare the data in advance. Every time a product is saved, we build one record with all the data needed for search: the name, the SKU in several spellings, categories and attribute values, all normalised (lower case, no diacritics, no hyphens). Search then runs on a single narrow table with FULLTEXT indexes instead of JOINs across half the database.

This is how popular WordPress plugins work: Relevanssi and SearchWP build their own index tables. PrestaShop has for years kept words in the ps_search_word and ps_search_index tables, with weights set in the admin panel (name, reference, descriptions, category, manufacturer, attributes, features). A simple version for WooCommerce can look like this:

CREATE TABLE wp_product_search_index (
    product_id BIGINT UNSIGNED NOT NULL PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    codes VARCHAR(255) NOT NULL,
    keywords TEXT NOT NULL,
    in_stock TINYINT(1) NOT NULL DEFAULT 0,
    FULLTEXT KEY ft_all (title, codes, keywords),
    FULLTEXT KEY ft_title (title),
    FULLTEXT KEY ft_codes (codes),
    FULLTEXT KEY ft_keywords (keywords)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

<?php
// Normalisation: lower case, no diacritics (Polish ones here) and no special characters
function normalize_search_text(string $text): string
{
    $text = mb_strtolower(wp_strip_all_tags($text));
    $text = strtr($text, [
        'ą' => 'a', 'ć' => 'c', 'ę' => 'e', 'ł' => 'l', 'ń' => 'n',
        'ó' => 'o', 'ś' => 's', 'ź' => 'z', 'ż' => 'z',
    ]);
    $text = preg_replace('/[^a-z0-9]+/', ' ', $text);

    return trim(preg_replace('/\s+/', ' ', $text));
}

// Refresh the product's record in the index every time the product is saved
add_action('woocommerce_new_product', 'update_product_search_index');
add_action('woocommerce_update_product', 'update_product_search_index');

function update_product_search_index(int $product_id): void
{
    global $wpdb;

    $product = wc_get_product($product_id);
    if (!$product) {
        return;
    }

    $sku = (string) $product->get_sku();
    // Save "XR-200" as "xr 200" and "xr200" so that every spelling matches
    $codes = normalize_search_text($sku) . ' ' . strtolower(preg_replace('/[^a-z0-9]/i', '', $sku));

    $terms = wp_get_post_terms($product_id, ['product_cat', 'product_tag'], ['fields' => 'names']);
    $attribute_values = [];
    foreach ($product->get_attributes() as $attribute) {
        $attribute_values = array_merge($attribute_values, $attribute->is_taxonomy()
            ? wc_get_product_terms($product_id, $attribute->get_name(), ['fields' => 'names'])
            : $attribute->get_options());
    }

    $wpdb->replace($wpdb->prefix . 'product_search_index', [
        'product_id' => $product_id,
        'title' => normalize_search_text($product->get_name()),
        'codes' => $codes,
        'keywords' => normalize_search_text(implode(' ', array_merge(
            is_array($terms) ? $terms : [],
            $attribute_values,
            [$product->get_short_description()]
        ))),
        'in_stock' => $product->is_in_stock() ? 1 : 0,
    ]);
}

We pass the customer’s phrase through the same normalisation function. A simple trick: shorten longer words by one or two letters and add an asterisk, which is a very simple way to handle word forms (in the example below, the Polish phrase “kurtka zimowa”, meaning “winter jacket”, becomes “kurtk* zimow*”). A match in the code counts more than in the name, the name counts more than the keywords, and products in stock rank higher:

SELECT product_id,
       MATCH(codes) AGAINST('kurtk* zimow*' IN BOOLEAN MODE) * 5
     + MATCH(title) AGAINST('kurtk* zimow*' IN BOOLEAN MODE) * 3
     + MATCH(keywords) AGAINST('kurtk* zimow*' IN BOOLEAN MODE)
     + in_stock * 2 AS score
FROM wp_product_search_index
WHERE MATCH(title, codes, keywords) AGAINST('kurtk* zimow*' IN BOOLEAN MODE)
ORDER BY score DESC
LIMIT 20;

Advantages: full control, no extra infrastructure and product codes that work exactly the way we want. Drawbacks: you have to keep the index up to date (CSV imports, bulk stock changes or ERP synchronisation can bypass the automatic index update, so a full rebuild run from cron is also useful), and there is still no typo tolerance or synonym support unless you add them yourself. A synonym dictionary is usually a separate table plus expanding the phrase before running the query.

Index in binary files: Lucene, Zend_Search_Lucene and Sphinx

Almost every serious search engine is built on the same idea: an inverted index. Instead of keeping a list of products and searching them for words, you keep a list of words and, for each of them, a list of the products it appears in, together with positions and frequency. Searching for a phrase then means a few reads and an intersection of lists, not scanning the whole catalogue.

Apache Lucene, a Java library developed since the late 1990s, stores such an index in segments: immutable binary files on disk that are merged into larger ones in the background. In the PHP world, Zend_Search_Lucene from Zend Framework 1 used to be popular: an implementation of Lucene written in pure PHP that kept the index in files in the application directory, with no additional server. Convenient to start with, but slow and memory-hungry with larger catalogues, and prone to locking problems with concurrent writes. Today it is a historical solution.

The second veteran is Sphinx: a separate server (searchd) that pulls data directly with an SQL query (the indexer tool) and keeps the index in its own binary files. It was very fast and spoke the MySQL protocol (SphinxQL), so applications queried it like a regular database. For years it powered search in many Polish online stores and websites. When Sphinx development slowed down and new releases stopped being published as open source, the Manticore Search fork was created in 2017. It is still actively developed today, with additions such as real-time indexes and vector search.

-- Manticore Search: a real-time table, used with a regular MySQL client
CREATE TABLE products (title text, sku string, price float, in_stock bool) min_prefix_len='3';

INSERT INTO products (id, title, sku, price, in_stock)
VALUES (1, 'Men''s winter jacket', 'XR-200', 399.0, 1);

SELECT id, title, WEIGHT() AS score
FROM products
WHERE MATCH('jack*') AND in_stock = 1
ORDER BY score DESC
LIMIT 20;

Elasticsearch and OpenSearch: the heavy artillery

Elasticsearch is a distributed search server built on Lucene. It communicates through a REST API and JSON, and in return offers everything that was missing before: language analysers, typo tolerance (fuzziness), synonyms, autocomplete, aggregations for filters with counts and scaling across many nodes. For Polish there is the official analysis-stempel plugin, and Morfologik provides more accurate, dictionary-based analysis.

An analyser is a recipe for processing text: splitting it into words, converting to lower case, reducing each word to its base form and removing diacritics. The same recipe is applied to the phrase the customer types, so different forms of the same words meet in the index in the same form. We create an index with this configuration using a PUT /products request:

{
  "settings": {
    "analysis": {
      "analyzer": {
        "polish_products": {
          "tokenizer": "standard",
          "filter": ["lowercase", "polish_stem", "asciifolding"]
        }
      }
    }
  },
  "mappings": {
    "properties": {
      "name": { "type": "text", "analyzer": "polish_products" },
      "description": { "type": "text", "analyzer": "polish_products" },
      "sku": { "type": "keyword" },
      "category": { "type": "keyword" },
      "price": { "type": "scaled_float", "scaling_factor": 100 },
      "in_stock": { "type": "boolean" }
    }
  }
}

The query (POST /products/_search) combines typo-tolerant search, filtering and aggregations, from which we build filters with counts in the sidebar:

{
  "query": {
    "bool": {
      "must": {
        "multi_match": {
          "query": "kurtek zimowych",
          "fields": ["name^3", "description"],
          "fuzziness": "AUTO"
        }
      },
      "filter": [{ "term": { "in_stock": true } }]
    }
  },
  "aggs": {
    "categories": { "terms": { "field": "category" } },
    "price_ranges": {
      "range": {
        "field": "price",
        "ranges": [{ "to": 100 }, { "from": 100, "to": 300 }, { "from": 300 }]
      }
    }
  }
}

The price for these capabilities is infrastructure. Elasticsearch runs on the JVM and needs a lot of RAM, so in practice it means a separate server or a managed service, plus synchronising data from the store (a queue, reindexing, error handling). For large catalogues it is the market standard: Magento has actually required Elasticsearch or OpenSearch since version 2.4, and WooCommerce can use it through the ElasticPress plugin, among others.

A word about licences: in 2021 Elastic changed its licence from Apache 2.0 to SSPL and the Elastic License. In response, AWS created the OpenSearch fork (Apache 2.0, now developed under the Linux Foundation), and in 2024 Elastic added an open source option again in the form of the AGPL licence. At the level of basic queries both projects are interchangeable for a store, although they diverge more in the details with every version.

The new wave: Meilisearch and Typesense

Between MySQL and Elasticsearch there is room for lighter engines designed from the start for “app-like” search: results as you type, typo tolerance with no configuration and sensible relevance from the first run.

• Meilisearch (Rust, open source). Installation is a single binary or a container, and indexing means sending a JSON file with products. Typo tolerance, filters with result counts, sorting and synonyms come as standard.
• Typesense (C++, open source). A similar philosophy, with the index kept in RAM, very low latency and built-in cluster support.

Both work with the InstantSearch library, which lets you quickly assemble a search frontend with suggestions and filters. The catch for inflected languages such as Polish: neither handles word forms well. Prefix search and typo tolerance partly mask the problem, but words whose stem changes still will not match. It is worth testing on your own catalogue before deciding. This is how you get started with Meilisearch:

# Meilisearch locally in a container
docker run -d -p 7700:7700 -e MEILI_MASTER_KEY='change-me-master-key' getmeili/meilisearch:latest

# Index settings: what we search in and what we filter by
curl -X PATCH 'http://localhost:7700/indexes/products/settings' \
  -H 'Authorization: Bearer change-me-master-key' \
  -H 'Content-Type: application/json' \
  --data-binary '{ "searchableAttributes": ["name", "sku", "category", "description"], "filterableAttributes": ["category", "in_stock"] }'

# Add products to the index
curl -X POST 'http://localhost:7700/indexes/products/documents' \
  -H 'Authorization: Bearer change-me-master-key' \
  -H 'Content-Type: application/json' \
  --data-binary @products.json

# Search with a typo and a filter
curl -X POST 'http://localhost:7700/indexes/products/search' \
  -H 'Authorization: Bearer change-me-master-key' \
  -H 'Content-Type: application/json' \
  --data-binary '{ "q": "wintr jacket", "filter": "in_stock = true" }'

Search as a service: Algolia, Doofinder, Luigi’s Box

The last option is not to maintain a search engine yourself at all. SaaS services take over the index, the infrastructure and often the whole search frontend (the suggestions window, filters, the results page). They are connected to the store with a plugin or a product feed.

• Algolia: the best known, very fast, with an extensive API for developers. You pay for the number of records and requests.
• Doofinder: focused on e-commerce, with ready-made integrations for WooCommerce, PrestaShop and Shopify, among others; no coding required.
• Luigi’s Box: a Slovak provider popular in Central Europe, strong in search analytics and recommendations.

Pros: fast implementation, analytics (what customers search for and do not find, which is invaluable for the purchasing team) and manually pinning selected products to specific phrases. Cons: a subscription that grows with traffic and catalogue size, dependence on the provider and product data outside your own infrastructure.

Comparison

What to choose?

• Up to 1,000 or 2,000 products: MySQL FULLTEXT or a plugin such as Relevanssi or SearchWP. Make sure you support SKU search and diacritics normalisation.
• A few thousand to tens of thousands of products, with filters and typo tolerance needed: Meilisearch or Typesense; if inflection is crucial, Elasticsearch or OpenSearch with a language analyser.
• A large catalogue, many filters, B2B with product codes: Elasticsearch, OpenSearch or Manticore, with well-designed data synchronisation.
• No technical team and time matters: SaaS, but calculate the cost at your real traffic, not on the starter plan.

Whatever the engine, working on the data brings the biggest gains: complete attributes, synonyms added based on the report of searches with no results, and a sensible order (products in stock and bestsellers higher). The report of phrases that returned nothing is the cheapest source of knowledge about what customers are looking for and what you do not have in your offer. Such a report is easy to build, for example in PostHog.

So where does AI come in?

All the solutions described here search for words. Meanwhile, customers increasingly describe a need: “something warm for cycling in November”, “a gift for a dad who loves barbecuing”. None of the methods described will turn that into “winter cycling gloves”. This is where vector search based on AI models comes in, that is, search by meaning rather than by words. A combination of both approaches is also used more and more often. Interestingly, most of the engines in this article (Elasticsearch, OpenSearch, Manticore, Meilisearch, Typesense, and PostgreSQL with the pgvector extension) can already store and search vectors. We show how to build this in practice in the next article of our series on AI in online stores.