Search for a command to run...
Every hard problem in our database is, at the bottom, the same problem: the same concept is written a thousand different ways, and we have to treat those thousand strings as one thing before we can count anything.
"Creatine monohydrate," "creatine supplementation," "CR," "creatine ethyl ester" — these are not the same string, and a database that treats them as different will tell you creatine has been studied four times when it has been studied four thousand. There are about 476,000 distinct outcome strings in our data, and a similar long tail of supplement names. This is a post about how we fold that mess into something queryable, and the one trick that made it fast.
The first move is the obvious one. Every variant string gets canonicalized. "Creatine monohydrate" and "creatine supplementation" both point at a single canonical supplement record. Each canonical record holds its clean name, a normalized form for matching, the list of variant strings that map to it, how many claims use it, and — when we can find one — a medical subject heading that ties it to the formal vocabulary the literature uses.
This collapses the spelling problem. After canonicalization, "creatine" is one row no matter how it was phrased in the original paper. Same for outcomes. Every claim in the database now points at a canonical supplement and a canonical outcome instead of a free-text string.
So far so good. This is the part everyone does. The problem is that it is not enough.
Even after canonicalization, you have a problem. There are still hundreds of thousands of canonical outcome entities, because outcomes are genuinely numerous — "grip strength," "muscle strength," "knee extension strength," "one-rep max," "lean body mass" are all real, distinct outcomes that happen to cluster around the same idea. Canonicalization does not merge them, and it should not, because they are not the same outcome. They are related.
Which means: asking "everything about strength" is still a fuzzy question. You would have to find every canonical outcome that means something like strength, which is either a slow keyword search across hundreds of thousands of rows or an embedding similarity search at query time. Both work. Both are slow. Both are hard to make agree with a precomputed count, because the set of "things that mean strength" depends on your threshold at the moment you ask.
This is the wall. You can canonicalize forever and still hit it. We needed a different move.
Here is the trick. Instead of solving "what is a concept?" at query time, we solve it once, ahead of time, and store the answer as an index.
We look at every canonical entity and rank them by how many claims use them. The most common ones — the top few hundred — are almost always broad concepts: strength, inflammation, sleep quality, blood glucose. These高频 entities become what we call signal keywords. They are the load-bearing concepts that most claims cluster around.
Then, for every other canonical entity in the database, we check: does this entity's name contain one of those signal keywords, as a whole word? If "grip strength" contains "strength," we draw a link from the grip-strength entity to the strength signal keyword. If "muscle strength" contains it, same link. We do this for all 476,000 outcomes against all the signal keywords, with a strict word-boundary match so that "strength" does not accidentally link to "strengthening exercises" or whatever else.
What you end up with is a many-to-many map. Every signal keyword is linked to every entity that mentions it. And every entity that mentions no signal keyword — the long tail of rare, specific outcomes — simply links to itself, so nothing is left out.
Now the query "everything about strength" is not a fuzzy search. It is a lookup. Find the strength signal keyword. Fetch every entity linked to it. You now have the exact set of canonical outcomes that mean something like strength, and you got it by following stored links, not by running a similarity computation in the moment.
This is fast. It is also deterministic, which matters more than speed. A precomputed consensus count and a live page query now expand "strength" through the exact same set of links, so they will always agree on what counts as strength. The fuzziness that used to live at query time — what threshold, which embedding model, how close is close enough — is gone, replaced by a fixed, inspectable index.
And the long tail is handled cleanly. The rare outcome that is too specific to be a signal keyword and does not contain one still gets represented, by bucketing to itself. Every claim lands in exactly one bucket. Nothing is double-counted, nothing is dropped.
The cost is building it. Drawing the links means comparing every signal keyword against every entity, which is a lot of matches — on the order of hundreds of millions of comparisons. It takes minutes, not milliseconds. But it runs in a pipeline, ahead of time, and the result is a static table. We pay the cost once per rebuild, and every query afterward is cheap.
This is the trade underneath the whole design. We moved the expensive, fuzzy, disagreement-prone work out of the query path and into a batch that runs when the data changes. Queries became lookups. Counts became aligned. The database stopped being a place where you search for meaning and became a place where meaning has already been indexed.
The pattern, stripped of supplements, is this: when a concept is fuzzy and frequent, do not solve it at read time. Identify the small set of concepts that carry most of the weight, build a fixed index from everything that relates to them, and let the long tail fall back to itself. Then every consumer of the data — every count, every page, every agent tool — expands through the same index and agrees by construction.
It is not the cleverest technique in the system. It is the one that made the rest of the system possible.
