The tea shop's catalog hit 20,000-plus products, and search gave out first. The old query was a single LIKE '%keyword%'. A product named 'Tieguanyin Light-Aroma 250g' would sort of show up when you searched 'Tieguanyin,' but 'light-aroma Tieguanyin' returned nothing.
Why Not Just Add an Index
A LIKE with a leading wildcard cannot use a B-tree index. We considered full-text indexes, but product names mix Chinese, numbers, and units, plus a pile of aliases — reworking that is no cheaper than changing solutions. At 20,000-plus rows, Elasticsearch is arguably overkill, but the client also wanted filtering by category, origin, and price range, so we did it all in one go.
V1 Tokenization Went Sideways
We didn't configure the mapping carefully and just loaded the data. Search 'Anxi Tieguanyin' and the default analyzer split every character; the results filled up with unrelated products containing only 'An' or 'Xi,' and the ranking was all over the place. Searching 'Tieguanyin' did return results, but the most relevant ones weren't on top.
We redid the mapping:
- Product names use ik_max_word for indexing — split as finely as possible so every phrasing can hit.
- Queries use ik_smart — coarser tokenization, so the query itself doesn't get shredded into single characters to match against.
- A separate pinyin field, so 'tieguanyin' works too — support said some people type pinyin out of habit.
- Aliases get their own field with a boosted weight: 'Tieguanyin' and 'Guanyin Wang' point to the same batch of goods.
Then we ran thirty real search queries, printed the top five results for the client's operations team, and got six marked as unsatisfactory — another round of weight tuning followed.
How Sync Stays Current
No dual writes. MySQL is the source of truth; the products table has an updated_at column, and a scheduled job scans the increment every ten minutes, reindexing whichever ids changed. Deletions are soft — the index marks the status accordingly.
The worst case is a ten-minute delay, and the client accepted that: a mistyped price is better shown a little late than pushed out instantly. For genuinely urgent cases, the admin panel has a 'sync now' button that operations presses themselves.
The Six Unsatisfactory Terms
We went through the six flagged terms one by one. Two were uncovered aliases: 'Guanyin Wang' didn't surface Tieguanyin, and 'Da Hong Pao' was split into 'da', 'hong', 'pao'. We added them to the alias field with a weight one tier above product names. Three were numbers and units: '250g', 'half a jin', 'one-jin pack' — none had been indexed. We later built a dedicated sub-field for the spec, so searching 'half a jin' now hits the 250g batch.
The last one was 'Tieguanyin light-aroma' — users searching the product name and the aroma type together. Aroma used to live only in the filter options, never in the search fields. We added it, but kept the weight low, so matching on aroma alone doesn't push other teas to the top.
We reran the thirty terms. One unsatisfactory result remained in the top five — an obscure place of origin for a tea the catalog simply doesn't carry. Operations said leave it; it doesn't exist anyway.
The Pinyin Field's Little Pitfall
After the pinyin field shipped, 'tieguanyin' worked but 'tie guan yin' with spaces didn't. The analyzer configuration didn't handle spaces; we now strip spaces from the query string before passing it in, and built both full pinyin and initials, so 'tgy' works too. Support was pleased — some people just love typing three letters.
The Index Rebuild
Changing the mapping means rebuilding the index; a full reload of 20,000-plus rows takes about four minutes. No downtime: we create a new versioned index, load it, point the alias over, keep the old one for a day, then delete it. We borrowed that pattern from canary releases and used it somewhere new.
The ten-minute incremental sync has held up ever since, with one scare: ops accidentally stopped the job for three hours, and operations noticed new products weren't searchable. One backfill run fixed it, and the job got an alert afterward. The zero-result terms report now mails itself to operations monthly — we don't export it by hand anymore. Last month's top term was 'gift bag,' and indeed the catalog has no such product.
An Unexpected Use
After launch, the most-searched terms weren't tea names but 'gift,' 'sample,' and 'gift box.' In the LIKE era those three basically returned nothing, because they lived in the descriptions, not the product names. Indexing the first 200 characters of the descriptions finally made them usable.
Operations exports a 'zero-result search terms' list every month to see what users looked for and didn't find. That sheet is more practical than any search-optimization advice we could offer.
