Moving catalogue search off MySQL and onto Elasticsearch

Catalogue search was a LIKE '%term%' across three columns, joined to two more tables, ordered by a CASE expression that approximated relevance. It took ten seconds on a good day and returned results in an order nobody could defend. This is what replacing it looked like, including the parts that are not about speed.

The symptom

# Query_time: 9.847  Rows_sent: 40  Rows_examined: 1204418
SELECT p.* FROM products p
  LEFT JOIN brands b ON b.id = p.brand_id
  LEFT JOIN product_descriptions d ON d.product_id = p.id
 WHERE p.name LIKE '%ray ban aviator%'
    OR b.name LIKE '%ray ban aviator%'
    OR d.body LIKE '%ray ban aviator%'
 ORDER BY CASE WHEN p.name LIKE 'ray ban aviator%' THEN 0 ELSE 1 END, p.name

Ten seconds, and worse than that: searching “rayban” found nothing, “Ray-Ban” found nothing, and a misspelling found nothing. The customers who searched successfully were the ones who already knew the exact product name, which is the opposite of what search is for.

Why it happens

A leading wildcard makes a B-tree index unusable — the index is ordered by prefix, and %term has no prefix to seek to. So every one of those three LIKE clauses is a full scan of its table, and the OR means all three run.

The deeper problem is that a relational index answers “does this string start with” and search needs “is this document about”. Those are different questions, and no amount of tuning turns one into the other. Full-text indexes in MySQL 5.6 close part of the gap and still have no useful relevance model, no stemming worth the name, and nothing for the hyphen in Ray-Ban.

The fix

The mapping is the design decision

Everything about how search behaves is decided in the mapping, before a single document is indexed. The most common mistake is to accept dynamic mapping and discover months later that a field was inferred as the wrong type from the first document that happened to contain it.

PUT /products_v1
{
  "settings": {
    "analysis": {
      "analyzer": {
        "product_name": {
          "tokenizer": "standard",
          "filter": ["lowercase", "asciifolding", "product_synonyms"]
        }
      },
      "filter": {
        "product_synonyms": {
          "type": "synonym",
          "synonyms": ["rayban, ray ban, ray-ban", "sunglasses, shades"]
        }
      }
    }
  },
  "mappings": {
    "product": {
      "dynamic": "strict",
      "properties": {
        "sku":      { "type": "string", "index": "not_analyzed" },
        "name":     { "type": "string", "analyzer": "product_name" },
        "brand":    { "type": "string", "analyzer": "product_name" },
        "body":     { "type": "string", "analyzer": "product_name" },
        "price":    { "type": "integer" },
        "in_stock": { "type": "boolean" }
      }
    }
  }
}

dynamic: strict is the setting worth arguing for: an unexpected field is rejected rather than silently typed, so a change upstream fails loudly at index time instead of producing a field that cannot be queried the way anyone expects.

The synonym filter is what fixes “rayban”. asciifolding is what makes a Turkish customer typing without diacritics find the product anyway, which on this catalogue mattered more than everything else combined.

Analyzers decide what matching means

The single most useful debugging endpoint is the one that shows what a string became. Almost every “search does not find an obvious result” report is answered here rather than in the query.

$ curl -s 'localhost:9200/products_v1/_analyze?analyzer=product_name' -d 'Ray-Ban Aviator'
{"tokens":[
  {"token":"ray",...},{"token":"ban",...},{"token":"aviator",...}
]}

$ curl -s 'localhost:9200/products_v1/_analyze?analyzer=product_name' -d 'rayban'
{"tokens":[
  {"token":"ray",...},{"token":"ban",...}
]}

Both produce the same tokens, which is why one now finds the other. That is the whole mechanism — the query goes through the same analyzer as the document, and matching happens on tokens rather than on strings.

The indexing pipeline and how it fails

A second store is only as good as its synchronisation, and the naive approach — index on save, inside the request — couples the checkout page to Elasticsearch being up.

// on write: enqueue, never index inline
class ProductObserver
{
    public function saved(Product $product)
    {
        Queue::push(new IndexProduct($product->id));
    }

    public function deleted(Product $product)
    {
        Queue::push(new RemoveFromIndex($product->id));
    }
}

// the job, which must tolerate the row having changed again
class IndexProduct
{
    public function handle()
    {
        $product = Product::with('brand')->find($this->id);

        if ($product === null) {
            $this->client->delete(['index' => 'products', 'id' => $this->id]);
            return;
        }

        $this->client->index([
            'index' => 'products',
            'type'  => 'product',
            'id'    => $product->id,
            'body'  => $this->document($product),
        ]);
    }
}

The job re-reads the row rather than carrying a snapshot, because two jobs for the same product can run out of order and the last write must win. Deleting when the row is gone handles the delete-then-index race without a special case.

What this does not survive is a bulk import writing with the query builder, which fires no model events. That path has to enqueue explicitly, and the nightly verification below is what catches it when someone forgets.

Reindexing behind an alias

A mapping cannot be changed in place, and mappings change — a new synonym, a new field, a different analyzer. Every one of those is a new index, which is only a non-event if the application never knew the index name.

$ curl -XPUT localhost:9200/products_v2 -d @mapping-v2.json
$ php artisan search:reindex --into=products_v2
Indexed 41,208 products in 4m12s

$ curl -XPOST localhost:9200/_aliases -d '{
  "actions": [
    { "remove": { "index": "products_v1", "alias": "products" } },
    { "add":    { "index": "products_v2", "alias": "products" } }
  ]
}'

Both actions apply atomically, so there is no moment where the alias points at nothing. The previous index stays for a day, which makes the rollback the same call with the arguments swapped — the cheapest rollback anywhere in this stack.

Verifying it worked

Latency is the easy half and not the interesting one. Search that is fast and returns the wrong things is worse than search that is slow, because nobody reports it.

# latency, same queries
# mysql: 9.8s p50, 14.2s p95, 40 results, no ranking
# elasticsearch: 24ms p50, 61ms p95

# relevance, on a fixed list of 60 real queries from the logs
$ php artisan search:judge
queries with the expected product in the top 3:
  before  22 / 60
  after   54 / 60

The judgement list is sixty real searches taken from the access log, each annotated by hand with the product the customer was obviously looking for. It took an afternoon to build and it is the only measurement that says whether search got better rather than faster.

The six failures were all the same shape: searches for a product the shop does not stock. Nothing fixes that, and knowing it is the whole reason to have the list.

Reconciliation, because the copy will drift

Everything above assumes the queue delivers and the jobs succeed. Neither is guaranteed, and the failure is silent — a product missing from the index is a product nobody can find, and nothing in the application notices.

// nightly: compare, report, do not repair
public function handle()
{
    $dbCount = Product::where('active', true)->count();
    $esCount = $this->client->count(array('index' => 'products'))['count'];

    $missing = Product::where('active', true)
        ->whereNotIn('id', $this->allIndexedIds())
        ->pluck('id');

    if ($dbCount !== $esCount || $missing->isNotEmpty()) {
        Ops::alert('search index drift', array(
            'db' => $dbCount, 'es' => $esCount, 'missing' => $missing->take(20),
        ));
    }
}

Reporting rather than repairing is deliberate. Reindexing the difference automatically makes the symptom disappear every night and hides whichever write path is bypassing the queue, which is the thing actually worth knowing. The first week found a bulk price update using the query builder, exactly as expected.

Degrading rather than breaking

Search is now a network call to a service that can be down, and the first instinct — let the exception propagate — turns a search outage into a site outage.

public function search($term)
{
    try {
        return $this->elastic->search($term);
    } catch (ElasticsearchException $e) {
        $this->log->warning('search degraded to sql', array('e' => $e->getMessage()));
        $this->metrics->increment('search.fallback');

        return $this->sql->search($term);   // slow, and it works
    }
}

Keeping the old LIKE query alive is unglamorous and it is the difference between a degraded search box and a 500 on the busiest page in the shop. The counter matters as much as the fallback: without it the site quietly serves bad search for a week and nothing says so.

What this costs

There is now a second datastore holding a copy of the catalogue, and it can drift. The nightly reconciliation is not optional — it compares document count and a checksum per product against the database and reports rather than repairing, because a silent repair hides whichever write path is bypassing the queue.

It is also a new thing to operate: a JVM with its own memory model, its own disk usage and its own failure modes, none of which the team had experience with. Budget for learning that rather than assuming it is a library. The first production incident was a heap setting left at the default, which is not a search problem and is now your problem.

And the application has to degrade rather than break. Search failing should fall back to the old LIKE query with a banner, not return a 500 — which means keeping the slow path alive rather than deleting it, and testing it occasionally so it still works when it is needed.