Re:Do We Still Have to Denormalize? Have Your Schema and Sea...
by radiospiel on Monday November 16, 2026 @02:20AM
(#50514969)
Indeed. The query should maybe be a UNION ALL over individual search results instead.
Re:Do We Still Have to Denormalize? Have Your Schema and Sea...
by onedognight on Monday November 16, 2026 @10:47AM
(#50518879)
They compare to UNION ALL in passing:
> Getting that original, natural SQL query down to 100 ms on a single core (12x-50x faster than the 84-line UNION ALL rewrite, and within range of a fully denormalized dataset) required rethinking how joins and search indexes interact
Re:Do We Still Have to Denormalize? Have Your Schema and Sea...
by stuhood on Monday November 16, 2026 @11:19AM
(#50519299)
It very much depends which noun you are searching for (e.g., what the shape of your search results will be):
The central query in the blog would likely be the type of query you'd use for the default top-level search box on StackOverflow, which is searching for posts and comments (e.g. questions and responses). The reason to include users in that query is two fold: 1) posts/comments from users who are relevant to your query should get a higher score, 2) you need the users to render the search results page anyway.
If you were doing only user search, then I agree: you probably would only search the users table.
Re:Do We Still Have to Denormalize? Have Your Schema and Sea...
by amluto on Monday November 16, 2026 @11:58AM
(#50519804)
If I’m searching for questions and answers, I don’t want an unrelated answer from a very high ranked user who happens to mention the topic in their profile.
Re:Do We Still Have to Denormalize? Have Your Schema and Sea...
by stuhood on Monday November 16, 2026 @12:10PM
(#50519960)
Possibly, yea: scoring can be subtle. For a query like this, you'd likely significantly "boost" (e.g. multiply) the scores from the questions and answers to ensure that they are prioritized. But including the user score as essentially a tiebreaker can be helpful.
I feel like I would probably execute three separate queries and merge the results myself. And the user query wouldn’t join against posts at all. Doing this would end up implicitly doing something like the optimization in the article.