Does mysql have cache about sql plan?

Question

If the same sql run many times from different sessions, will mysql parse the same sql many times? In oracle/sql server, the plan for a sql is cached and can be reused. Since it is told that parse and creating sql plan is costly, if mysql doesn't cache them, will it be a problem to parse it many time which could potentially cost a lot?

Answer 1

This is an existing MySQL Feature Request.

However, the last comments (in 2009) where along the lines that it's not clear it would offer any significant performance improvements and that it could lead to deadlock conditions.

Answer 2

For execution plan caching: I don't believe MySQL currently offers this feature.

MySQL does have a query cache: http://dev.mysql.com/doc/refman/5.1/en/query-cache.html

The query cache stores the text of a SELECT statement together with the corresponding result that was sent to the client. If an identical statement is received later, the server retrieves the results from the query cache rather than parsing and executing the statement again. The query cache is shared among sessions, so a result set generated by one client can be sent in response to the same query issued by another client.

I'm not sure how up to date this article is (2006), but it talks about these issues in detail: http://www.mysqlperformanceblog.com/2006/07/27/mysql-query-cache/
To the best of my knowledge, not much has changed since then in this regard.