WL#1569: Prepared Statements: implement support of Query Cache
Affects: Server-5.1 — Status: Complete — Priority: Medium
Make query cache available for queries running through prepared statement protocol. IMHO it will be needed more as the new protocol becomes popular. I think it will be enough to store bound data with query text and other parameters which already are stored in the query cache. Note added by Trudy Pelzer, 2006-08-06 At the Dev-MT Offsite meeting in Santa Cruz, Brian and Monty made the following time estimate for this task: - prepared statements (WL#1569 and others); 4+ mths * This is 4 tasks: ** WL#1569 "Prepared Statements: implement support of Query Cache" ** WL#? include all SQL ** WL#? update server for max ps statements ** WL#? make ps able to recreate itself if it doesn't exist * Brian: Prepared statements aren't useful until this 4-part task is complete. * Current estimate: Update for qcache: 1 mth. Update client library to recreate ps if it goes missing: 1 week. Update server to know about max number of statements it remembers: 2 weeks. Include all sql: 2 months (this includes needed discussion but not the extra time QA needs to adjust mysqltest for the new feature). Note added by Trudy Pelzer, 2007-05-25 Kostja and Guilhem agree that sufficient work has been done (fixing BUG#735 and BUG#26842) that this WL#1569 can be considered complete. The other 3 tasks that Monty and Brian determined should be done to make prepared statements fully useful will still be deferred; see WL#1803 for a complete list of work that still needs to be done to make prepared statements full-featured.
In order to support the query cache in the binary protocol, we need to enable caching of server responses to COM_EXECUTE commands. Insertion into the cache ------------------------ A query cache key consists of: - query attributes - query text For the binary protocol the cache key will be generated as follows: - key header will be extended with a flag that indicates that the data is in binary format. This will separate all COM_EXECUTE responses from all COM_QUERY responses in the cache. - to create a query text for the key we will expand parameter data into query text in the same fashion as it currently happens for replication of prepared statements: parameter markers in the query text are substituted with string representation of parameter data. This approach of generating the key has the drawback that the types of placeholders are not taken into account. As a result, the server will return the same response to a query which supplies the same data but in a different type. In theory, a change of the type of a parameter may lead to a different result set being returned to the client, but no example when this can actually happen is known at this point. Support of 5.0 SQL features: - currently if a query uses a stored function it is not cached. The reason is that a function may be non-deterministic or produce a side effect, and thus from the caching point of view is treated similarly to the built-in non-deterministic functions such as RAND() and CURRENT_TIME(). In future, when we start taking into account function characteristics, such as CONTAINS SQL and DETERMINISTIC, we will have to implement cache invalidation based on function name, to invalidate all responses that use a function when the function definition changes. This is not - if a query uses a view, it is inserted into the cache after the view was expanded (merged) and its tables were inserted into the list of all tables used in the query. Prepared statements in that respect are identical to the conventional statements, except that view expansion happens even earlier: at statement prepare stage. Cache lookup ------------ Cache lookup will happen at a subsequent call to COM_EXECUTE. The expanded statement text and environment information, such as the current time zone, character set, etc. will be used to generate a lookup key. Invalidation ------------ Query tables in a prepared statement are not changed between executions unless a prepared statement uses a stored function, trigger or a view. Fortunately, currently no query that involves stored functions and triggers can be cached. If a view was changed, the query cache result is properly invalidated, whereas the corresponding prepared statement is not (which is subject of BUG#12713). There is no problem for the query cache posed by this bug, as it the result in the query cache is invalidated independently of the statement cache. In all other respects PS query invalidation works identically to invalidation of conventional query results. Limitations ------------ Support of Query Cache for SQL prepared statements is not a subject of this worklog. Other ------- As current behaviour was clearly identified by many customers as a bug, a bug report was re-opened for this task: BUG#735 "Prepared Statements: there is no support for Query Cache". Kostja: This is the closest thing I was able to find.
Copyright (c) 2000, 2018, Oracle Corporation and/or its affiliates. All rights reserved.