A catalog search that finds “Miu Miu”: FULLTEXT on MariaDB

The Resolvi Desapegar catalog is made of luxury items, and many searches are by brand: “chanel bag”, “vivara earrings”, “miu miu bag”. That last one is the hard case. Without care, “miu” is too short for the text index, the search becomes just “bag” and returns the whole catalog.

The minimum word length problem

Most tables in the database are MyISAM, and MyISAM FULLTEXT ignores words shorter than ft_min_word_len (4 on the server in question). InnoDB uses a different setting, innodb_ft_min_token_size, set to 3, so it does index “miu”. The fix was to stop searching the products table: search has its own InnoDB table, used only for that.

A derived table, never the source of truth

The produtos_busca table stores, per product, a normalized text with title, brand, categories and reference, and the description separately. It is derived: if lost, it is rebuilt from the products. The index is refreshed when a product is saved in the admin panel, by an incremental job every 5 minutes and by a full rebuild overnight.

Normalizing both sides the same way

The indexed text and the typed query go through the same function: lowercase, no accents, plurals reduced to the stem, stop words removed. If one side changes without the other, search silently stops finding things, so any change to the normalization requires reindexing everything.

-- Simplified illustration of the idea (not the project's SQL)
SELECT produto_id
FROM produtos_busca
WHERE MATCH(doc) AGAINST ('+bolsa +miu' IN BOOLEAN MODE)
LIMIT 40;

Levels: from the most precise to the most permissive

The search starts by requiring every term in the title. If nothing comes back, it requires every term including the description, then accepts any term and, finally, falls back to a LIKE. Each level only runs if the previous one came back empty, which keeps relevance high without returning the whole catalog when someone types something very specific.

The hidden cost of the view

The product listing uses a view with a GROUP BY. In MariaDB, a view like that is fully materialized on every query, about 16 thousand rows, and filtering on it cost around 1.5 s even with an index on the base table. The pattern that fixed it works in two steps: filter the base table, collect the ids and only then query the view with IN (a literal list of ids). The same query dropped to about 25 ms. An IN (SELECT ...) subquery does not work, because the optimizer goes back to materializing everything.

Takeaways

• Check the table engine before assuming transactions, foreign keys or FULLTEXT behavior.
• A search index is derived data: plan how to rebuild it entirely.
• Normalize the query and the index with the same function, and test both ends together.
• Measure the real query. An index on the base table does not help if the filter is on the view.