Nothing new and interesting to report for the past few days. I’ve been continuing to work on the same knitting project. The old cat is getting older and is starting to have the types of issues that you would not be surprised to hear about in a 15-year-old cat.
But, never mind the boring stuff. Yesterday I made some materialized views in the database, and they were awesome!
I’ve mentioned this before, but the production database for running the site was primarily designed to run the site and do all the things that need to be done in order to keep things running. Most of the interactions with the database are like, “This user did that thing,” or “Find the things for this user.” On the other hand, the analysis queries are more along the lines of, “Find all the users in these categories who did those things during that timeframe and calculate the average number of points that they scored under certain circumstances.” The latter tend to be slower. They can be really slow. Far too slow to be allowed to run on the production server.
Most of my queries do follow certain patterns, though. I join together the same tables on a regular basis, and these joins are pretty time-consuming. A materialized view lets me do all my expensive joins and other slow operations once in the middle of the night and then save the output in a table-like structure (it is so table-like that I can even define indices on it!), and then at run-time, my dashboards can query the materialized view. Queries that used to take minutes to run will now execute in under a second!
It also makes my queries easier to read because I am not joining together a rat’s nest of tables in order to get the information that I am looking for.
More updates to come. The first legwarmer is almost done! The cat is going to try to take pills, so maybe I’ll have some interesting scratches and bite marks to show off.