Under the hood improvements!
Mad's Theatre is built on Drupal, which is a Content Management System (CMS) that is used to maintain all the Users and Content.
The data model under the hood for Drupal is actually pretty ingenious in it's design. Every piece of content is a "node" and is stored in a master node table that has the bare minimum details (title, who created it, when it was created, etc). All of the "fields" are then maintained in their own dedicated tables and can be shared across content. As an example when you create a Production and an Audition, you can add a website in the website field. That website field is a single table shared across two types of content.
Unfortunately, that data model is not always very efficient for surfacing data from the database to the page.
As this site has grown (there have been 400 Productions alone with almost 3,000 performances and hundreds-of-thousands of field values), it has become a bit of a drag to query all that data each time a page is visited. Ultimately, the performance wasn't all that bad (Google PageSpeed Insights still showed better performance than many other sites), but it was not what I'd like it to be. It is a priority for me to have a very quick and responsive website.
The solution is to shift all the heavy lifting from every page visit into a summary table of fields/nodes/roles/etc that is refreshed every hour and every time a piece of content is created/changed. There are still some at-the-moment queries that need to run every time you visit a Mad's Theatre page, but the new process is 75% faster than the previous process. Further below, I've provided more technical details for those mildly interested.
"Why don't you use Drupal's built in caching functionality to do this?"
I have and I do! Drupal's caching functionality is fantastic and there are parts of the website that utilize Drupal's cache. However, I think it's a cool thing to visit the site at 6:58pm and see a 7:00pm show on the page and then refresh that page at 7:02pm and see it disappear in favor of the very next show. To do this with Drupal's caching functionality is possible, but it still requires a lot of the same changes I just made in creating a module to know when to refresh the cache when the site needs refreshed data. So why not do it "my way" where I have a lot more control :)
More enhancements are still on the horizon. I haven't forgotten about the Volunteer Hub and other ideas for features. All-in-all, I love maintaining this site!
Benjamin
The Mad's Theatre Manifesto
This site was developed to be fully maintainable by nearly all of the individuals in our performing arts community. You can log in to control your information on this site.
This site was designed to be easy to use and promote informational fluidity. No fluff. Just information that organizes itself.
This site will be continually enhanced with new features and data.
This site will always be free to use for the Madison performing arts community.
Technical Details
- Changed Common Table Expressions (CTE) in view definitions into subqueries that show an approximate 10% better performance in MariaDB/MySQL databses. This compromises readability of SQL but provides better performance. For more details on CTEs vs Subqueries.
- Summarized the below tables that were previously in a single query in a view that ran every page visit into a single periodically refreshed table.
node_field_data
node__field_production_date
node__field_organization
node__field_other_organization
node__field_address_line_1
node__field_city
node__field_state
node__field_approval
node__field_open_external
node__field_link
node__field_override_publish
path_alias
user__roles - Created the Stored Procedure to refresh that table and also improved logging.
- Created the new Entity Update module that runs the new Stored Procedure every Cron run and also every time a piece of content is created and updated.
- Pointed all Production (the content type, not environment type) data on the site from the single query view to the new periodically refreshed table reducing page visit to data time roughly by 75%.
