Over the past few days, we’ve rolled out a bunch of query performance improvements. Here are the highlights;
- Game ratings materialized view: This change now makes it more likely that numbers like “number of reviews” etc. will be wrong. But, it seems to be faster. Generate a materialized view for
gameRatingsSandbox0by dfabulich · Pull Request #270 · iftechfoundation/ifdb · GitHub - Search by tags: We used to just search the entire list of games, and now, we use an index. Use gametags table when searching for tags by salty-horse · Pull Request #269 · iftechfoundation/ifdb · GitHub
- Removed personalized recommendations on the home page: On the home page, if you’re logged in, we’d try to calculate which users were similar to you, by finding who gave similar ratings to you. (We called this
user_proximity.) Then, we’d find games that similar players had rated highly, and present a short random list of them in the footer of the home page. It never worked well. We’ll need to turn this off “for now,” but I expect we’ll leave it turned off forever-ish. (We now just show a few random top-rated games that you haven’t marked as “played” before.) Disable user-proximity query temporarily by dfabulich · Pull Request #274 · iftechfoundation/ifdb · GitHub - Removed automatic cross-recommendations on game listings: On each game listing page, people can manually add cross recommendations, but the system would try to automatically cross recommend some games. The algorithm was very slow and bad, even worse than the user proximity query. I again expect we’ll leave this turned off forever-ish. Temporarily disable automatic cross recommendations by dfabulich · Pull Request #280 · iftechfoundation/ifdb · GitHub We now only show manual cross-recommendations, and hide the section if no one has manually recommended similar games.
- Search by author: Now we use a fulltext index. (I think it might be a bit more permissive than our old query? But the sort order is the same, so you probably won’t notice any difference.) Add fulltext index for author search by dfabulich · Pull Request #277 · iftechfoundation/ifdb · GitHub
- Implicit search by TUID: I’d implemented a thing back in October where you could type a game’s TUID in the search box, without typing
tuid:first, and you’d automatically go straight to the game. But the way I wrote it deoptimized the search-by-text feature. I’ve re-implemented it so it should be fast and correct. - Implicit search by IFID: And now we have implicit search by IFID, too, optimized by an index. This finally properly fixes Can't select game by TUID or IFID when editing Cross-References · Issue #300 · iftechfoundation/ifdb-suggestion-tracker · GitHub where the “Select a Game” popup on cross-recommendations, competitions, etc. would say “Search for a game by title, IFID, or TUID” but it was lying, you could only do regular searches, and search by IFID only with
ifid:ortuid:parameters. - Optimized search by plain text: We had a fulltext index for title, author, description, and tags, so if you search for a word, we’d try to find it in any of those places, but we’d also search the title specifically for partial matches, which convinced the database that it wasn’t worth checking the index; it just scanned the entire
gamestable every time you searched by plain text. But that was silly, because the fulltext index already supports partial matches, so I just removed them. (I’ve confirmed that searches forounterfeit Monkeywithout the C still returned Counterfeit Monkey.) As far as I can tell, the search results are exactly the same as before, except a zillion times faster. Remove LIKE clauses from fulltext search, to reactivate fulltext index by dfabulich · Pull Request #276 · iftechfoundation/ifdb · GitHub - Frequent Fiction materialized view: Now we compute Frequent Fiction scores every five minutes, instead of computing them as you query for them. Create a materialized view for userScores by dfabulich · Pull Request #272 · iftechfoundation/ifdb · GitHub
There are still just a couple slow queries left. https://github.com/iftechfoundation/ifdb-suggestion-tracker/labels/slow%20query We’ll continue to whack these moles over time.