When does an entry holding only the identifiers a query returned pay off, given any write to its tables voids it?

Alongside entries keyed by identifier, the same store can hold an entry keyed by **a query and its parameters**, whose payload is the identifiers that query returned. It is the part of the mechanism that most often disappoints, and knowing exactly why is the point of the question. ## What the entry holds The key is the statement text together with every parameter value and any paging arguments — different parameters mean different entries. The payload is deliberately thin: **only the identifiers of the rows that matched**, in order, and not the rows themselves. Storing rows here would duplicate the identifier entries and create a second place for the same row to go out of date. ## The validity rule Because the payload is a set of matching identifiers, the entry stops being correct as soon as *the set of matching rows could have changed*. The layer cannot tell whether a particular write changed the match, so it uses the conservative rule it can afford: it records the time or version of the last write **per table**, and treats the entry as valid only while none of the tables the query read has been written since the entry was stored. The consequences follow directly: - one insert anywhere in a table voids **every** entry over that table, including entries whose own rows were untouched - a query joining three tables is voided by writes to any of the three, so the odds get worse with each join - the granularity is the table and never the row, so "we only update rows nobody queries" does not save you On a table written even a few times a minute, entries over it rarely survive long enough to be reused at all. ## The second lookup A hit does not hand back rows. It hands back identifiers, which the layer then resolves — from the identifier entries when they are present, from the database when they are not. So: - the payoff depends on the identifier entries behind it; without them a hit turns one statement into many loads and can be **slower** than the query it replaced - the statement is skipped, but the assembly work — building instances, wiring links — is still paid on every hit - what you actually save is one round trip plus the engine's work for that query, which for a cheap indexed lookup is not much ## Why it usually loses to caching the finished result Caching the assembled output above the mapper — the transfer model the caller actually asked for — removes the statement *and* the assembly, and lets you pick the key, the lifetime and the trigger that clears it. The query-keyed entry offers none of those choices: the key is the statement, the lifetime lasts until anybody writes one of the tables, and the second lookup comes with it. What you give up by caching higher is identity and deferred links in the cached copy, which is a separate decision — but on cost alone the higher cache usually wins. ## The narrow case where it does win All of these at once: 1. the tables involved are **almost never written** — a few times a day, not a few times a minute 2. the query is **hot**, run on a large fraction of requests 3. the **parameter space is small**, so entries are reused instead of each request minting a new one 4. the **result is small**, and its rows are already resident as identifier entries 5. the query itself is **not cheap** — a sort or an aggregate-shaped filter, not a primary-key lookup A small, hot, almost-never-written lookup query satisfies all five. Very little else does, which is why the honest default is to leave this off and cache by identifier only. ## The same trick, keyed by an owner A collection entry — the members of one object's association — works the same way: it stores only the member identifiers and leans on the identifier entries to produce objects. The difference is the key and the voiding rule. It is keyed by the owning row's identifier and voided when that collection changes, rather than by any write to the member table, which makes it far more durable than a query-keyed entry and much easier to justify. ## What an interviewer is listening for - that you know the payload is identifiers rather than rows - that you can state the table-granular validity rule and its blast radius - that you can explain how a hit can end up slower than a miss - that you reach for a short list of conditions instead of answering "it depends"