MariaDB Fulltext Search plus LLM Query Expansion
Replace LIKE with a MariaDB FULLTEXT index, then add LLM query expansion: two layers that keep working when the index or the LLM fails.
I was building a search endpoint for a small article CMS. Users type a few words, the app searches title, excerpt, and content columns. My first implementation was straightforward:
WHERE title LIKE '%term%'. That was it. It worked on my dev machine, then the article count grew and the queries slowed down. The results were blunt, too: LIKE '%design%' matches "redesigned" and "designation" indiscriminately. No ranking either. You either matched or you didn't.
My first guess was to add LIKE '%word1% OR %word2%'. Which is obviously wrong syntax, but the instinct was to toss in more words and hope for better results. That's not how SQL works.
The MariaDB FULLTEXT Index
MariaDB ships with a FULLTEXT index type [1], a special index purpose-built for text search across one or more columns. Instead of scanning every row with a LIKE, the index lets the engine match tokens directly and even rank results by relevance.
The syntax is clean:
CREATE FULLTEXT INDEX IF NOT EXISTS ft_articles_search
ON articles_posts (title, excerpt, content)One statement, placed in the FastAPI lifespan startup hook so it runs once per process. Queries then use MATCH(title, excerpt, content) AGAINST(:terms IN NATURAL LANGUAGE MODE) AS score, ordered by that score. MATCH takes the column list, AGAINST takes the search string plus a modifier [4], and the official docs show exactly this AS score pattern: built-in relevance ranking, no custom scoring logic.
I added it to the repositories.py as a dedicated search path, with a LIKE fallback that escapes % and _ for environments where FULLTEXT isn't available (SQLite in dev). Two branches, same function signature, caller doesn't care which one runs.
The Idempotent Bootstrap
The index lives on MariaDB, but my app starts in different environments. I didn't want to maintain a separate migration script for one index.
The fix: bootstrap the CREATE FULLTEXT INDEX IF NOT EXISTS statement from FastAPI's lifespan startup hook. It runs once per process, idempotent by definition. If it fails (wrong permissions, SQLite in dev, whatever), a module-level flag flips to False and the search silently falls back to LIKE. No error, no warning, no broken page. Users just get slightly worse results.
There's a hard constraint worth repeating: the columns in MATCH(title, excerpt, content) must exactly match the columns in the index definition. Miss one and results come out wrong, or the query errors outright depending on the version. That constraint is why the column list lives in one place in the code instead of being repeated.
LLM Query Expansion
FULLTEXT got the core search working well. But users type natural-language fragments, not clean keywords. Someone might search "how to handle dates in python"; that's a lot of stopwords that MariaDB drops entirely in NATURAL LANGUAGE MODE [1]. What's left is just "handle dates python", missing synonyms like "timezone" or "naive" that could surface better results.
The idea: take the user's query, ask an LLM to expand it into 3-8 related keywords, then OR those extra terms into the MATCH ... AGAINST string.
I built a small llm_client.py wrapping an OpenAI-compatible API. The client is only marked configured=True when three environment variables are all set: base_url, api_key, and model. If any one is missing, the entire LLM feature vanishes: no partial state, no warnings.
The expand_query() function sends a prompt asking for a JSON array of keywords, strips any markdown code fences from the response, validates it's a list of strings. If anything fails (garbage reply, 8-second timeout, malformed JSON), it returns None. The search route receives None and proceeds with the original query, unmodified. No expansion, no error message, no retry.
This is the design principle: the LLM layer is purely additive. If it dies mid-request, search still works. It just stops getting the bonus keywords. Users won't know, and that's fine.
The complete() method catches every exception and returns None. This sounds defensive, and it is. I'd rather have silent degradation than a search page that throws 500s because an LLM provider went down.
When expansion succeeds, the extra keywords get OR'd into the search terms. The MATCH ... AGAINST string becomes something like "handle dates python timezone naive utc" instead of just "handle dates python". The quality improvement is noticeable but I haven't measured it formally: more results feel relevant, fewer dead ends.
Why This Layered Approach Works
Each layer stands on its own. LIKE fallback works everywhere, including SQLite during development. FULLTEXT index is a drop-in upgrade that runs automatically. LLM expansion is a bonus that can disappear without consequence.
No single layer is a hard dependency. The startup bootstrap handles the FULLTEXT-or-not decision once. The LLM client handles its own failure internally. The search function handles both.
The layering also kept the work small. The LIKE path is a handful of lines. The FULLTEXT bootstrap is one statement plus a flag. Most of the actual effort went into the LLM client, and half of that was making the prompt return valid JSON reliably.
The alternative I considered was Elasticsearch or Meilisearch. That's a separate service to run, monitor, and keep in sync. For a modest article count on a single-server app, MariaDB's built-in FULLTEXT plus a bit of LLM sugar gets me 90% of the way there with zero infrastructure overhead.
Related reading: when naive datetimes broke my MariaDB API and routing LLM calls through a container.