Developer Update November 14th, 2021 (Patreon)

Hey all, today I spent some time doing performance optimizations!

I am happy to report that the top pages are now much, much faster thanks to some query adjustments and better indexes. 

Originally the vote counts (likes, trash) were done via an appends query that ran a count(). These were being added to the Waifu at runtime from the framework rather than done in the same query. Earlier this year I moved the votes to a set of 2 queries that were done via a scope in the same select statement. This was more performant and cut down on the executed queries, but we still had an issue with performance.

Today I finally sat down and ironed it out and realized how slow INNODB is at running selects when the indexes are not created that span the whole scope of the query. The columns were indexed separately, but not together. 

With an absolutely huge seed on my local database I was to refine the query and indexes and get it running much better. I shouldn't have to deal with this issue again in the near future.