The solution to the issue of slow queries on a site that has a very active comment ‘gang’ (there are over 150K comments on that site, with about 50-100 added each day) (sort of a long answer, but the details might help others):
The first step was to ask the hosting company to move the shared hosting to another, more efficient server. Since this site has been around for many years, it’s possible it was still on it’s original server, with more powerful servers available after the sites’ hosting was started. This is just speculation, though.
But a move to a newer server improved overall response times, so pages wouldn’t time out, but database queries will still slow. According to the WP Query plugin, the typical page load of the current page had about 80-90 queries, with a query time of about 11-15 seconds. These numbers were consistent over several days.
So, the next step was to see if the SQL server could be faster. Note that the database had already been optimized. An caching did not help, because of the number of comments added each day. (In fact, adding caching slowed things further, as the page was never ‘static’ enough for caching to be effective.)
The hosting company investigated, and (after suggesting the standard ‘optimize your database’ answer), determined that the new server was in a data center on one side of the country (US), and the SQL server in a different data center on the opposite side of the country.
The hosting company moved the database to a newer, more powerful SQL server in the same data center as the hosting (shared) server.
This improved query response times from 11-15 seconds down to 1-3 seconds; a significant improvement.
To sum up:
- a more power shared server was used
- the database was moved to a SQL server in the same data center as the shared server
After one day on the new SQL server, the changes have apparently resolved the issue of slow page loads, slow queries, and timeouts (including 500 errors).