0

If a query is executed from the MySQL command line, then KILLed it while it is running will the query and its partial results* be in the query cache and usable to subsequent runs of the identical query?

For simplicity's sake, let's say this is a plain old SELECT with no subqueries.

The list of exclusions on this page covers cases in which queries will not be cached such as the queried table changing between executions, and certain operations that are by nature not identical, but I do not see interrupted or killed queries listed anywhere. It must not be that a killed query is treated the same as one that ran to completion, but if it met all of the other criteria for caching it would already be cached* before it got killed, so I'm wondering what happens next.

MySQL 5.6


*I'm making several assumptions here.

1

Yes and no.

The Query cache will not see, much less keep, the partial result. The page you referenced is talking only about the Query cache. The QC is populated only after the complete result is built. This gives a significant boost for a repeat of the identical query. (Similar queries are not helped any by the QC.)

InnoDB's buffer_pool (and the equivalent in MyISAM) caches whatever blocks are read from disk. Blocks that were read from disk before the killed was process will probably still be in RAM. This will provide some performance boost for repeated executions of the same or similar queries, whether killed or not.

  • So the query is checked against the cache before execution, and tables referenced in it are purged before execution, but only stored (if it makes it that far) after execution? – WAF Jul 20 '16 at 12:03
  • 1
    Check before execute - yes. Purge happens on every insert/update/delete; it removes all QC entries for the table(s) modified. The QC, is virtually useless for a frequently-modified table. What is stored in the QC: The hash of the SELECT, plus the entire result set. (Nothing stored until finished.) – Rick James Jul 20 '16 at 18:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.