I have a website algebra.com, where volunteers answer students'
questions for free.

It has three tables relevant to discussion, such as

- questions
- answers
- users

To produce web archives of old answered questions, I had to join all
three tables. The pages present subsets of archives by category, split
to about 50 answers per webpage (using "LIMIT start, count" notation).

This resulted in a huge amount of relatively expensive JOIN queries
(taking 0.5 seconds each on a 4 year old server). MySQL load average was
about 150%.

I created a poor man's "materialized view" table, that is the required
join. I update it every time a new answer is added, however, I do not
update it when less important information changes, such as tutor score
values.

Additionally, I fully rebuild this materialized view every hour from
crontab and sort it properly right then.

The result is really, great, queries now take 0.15 seconds each and
MySQL CPU load average dropped to about 15% (about 10 times or so, not
sure exactly, but many times).

i