Can you think of an example when it would be beneficial to have the Query Results cache time be longer than the Rendered Output time? or vice versa?
First some explanations:
Caching your query results, means you store the results. So the next page load you don't have to perform your heavy time consuming SQL query again.
Caching the rendered output means you cache the generated HTML output; the system does not have to render the page again.
It would make sense to have most of these exipire at the same time, after all, if the data changes, you'd want to show the changes to the user.
The only situation I can think of to cache the query results longer than the SQL data, is in a situation where the template file changes very often. Your theme might for instace randomly change the color of each table row, or show the current time (provided via a theme variable and a preprocess function) on the display. You might want to keep this out of the cache to give the user the impression the data is current, while still being able to serve the page relatively fast.
Caching the rendered output longer than the query seems like a bad idea. You'd be performing the resource intensive SQL query for new data, yet discard the data directly because you choose to display the old data instead. So I'd recommend against doing that.
Below are some situations (cases) that are impacting the choices you'd make for Query results cache time:
Case 1 - Your resources are not unlimited
A query that is extremely expensive to execute (eg the number of server resources you'd need for it, like memory, etc). And those resources that you'd need for it are limited.
Case 2 - Your data only change periodically
A query of which you are sure that the data to which these queries only get updated once a day, week, month, year.
Case 3 - Up-to-date information is not free
Assume your data are about current values (market values) of stocks, goods, whatever. Users who are willing to pay a premium price get updates every X seconds / minutes, while users not willing to pay that price only get those updates every day or so.
A sample could be that you're using this view on a site where you're trying to implement the BigPipe technique, similar what is done in Drupal 8's BigPipe module. Here is a quote from its project page:
Sends pages in a way that allows browsers to show them much faster. First sends the cacheable parts of the page, then the dynamic/uncacheable parts.
Refer to the great videos on this project page to get the idea, and/or to these related links:
- Community documentation about "Cacheability of render arrays".
- Drupal core issue about "BigPipe for auth users: first send+render the cheap parts of the page, then the expensive parts".
IMO selecting a value for either of those 2 caching options should be done independent from the other caching options. And even though their values may be set the same, there is no real reason why they should be the same.