How does the MySQL JDBC driver handle prepared statements

Imagine having a tool that can automatically detect JPA and Hibernate performance issues. Hypersistence Optimizer is that tool!

Prepared statement types

While researching for the Statement Caching chapter in my High-Performance Java Persistence book, I got the chance to compare how Oracle, SQL Server, PostgreSQL and MySQL handle prepare statements.

Thanks to Jess Balint (MySQL JDBC driver contributor), who gave a wonderful answer on StackOverflow, I managed to get a better understanding of how MySQL handles prepared statements from a database performance point of view.

Basically, there are two ways of preparing a statement: on the server-side or on the client-side.

Server-side prepared statements

The most common type is the server-side statement, which requires two database round-trips:

  • The driver submits a prepare request and the database parses the statement into a query tree, which can also be transformed into a pre-optimized tree structure. Because it is very difficult to build an execution plan without the actual bind parameter values, the execution plan is deferred until the statement gets executed
  • The execution request contains the current bind values, which the database uses to transform the parse tree into an optimal execution plan. The executor takes the plan and builds the associated result set.

If the data access logic doesn’t cache prepared statements, the extra database round-trip can actually hurt performance. For this purpose, some database systems don’t default to server-side prepared statements and execute a client-side statement preparation instead.

To enable server-side prepared statement, the useServerPrepStmts property must be enabled.

Client-side prepared statements

When the statement is prepared on the client-side, the bind parameter tokens are replaced with actual parameter values prior to sending the statement to the database server. This way, the driver can use a single request to fetch the result set.

I'm running an online workshop on the 27th of May about High-Performance SQL.

If you enjoyed this article, I bet you are going to love my Book and Video Courses as well.

Caching statements

In a high-performance OLTP system, statement caching plays a very important role in lowering transaction latencies. To avoid preparing a statement multiple times, the MySQL driver offers a client-side statement cache. Being disabled by default, the cache is activated by the cachePrepStmts Connection property.

For client-side statements, the tokenized statement structure can be reused in-between different preparing statement calls. The cache is bound to a database connection, but when using a connection pool, the physical connection lifetime spans over multiple application-level transactions (so frequently executed statements can benefit from using the cache).

For server-side statements, the driver caches the ServerPreparedStatement, as well as the check for server-side statement support (not all statements are allowed to be prepared on the server-side).

Caching statements can have a significant impact on application performance. If you are interested in this topic, then you might as well check High-Performance Java Persistence since it dedicates one chapter to this particular topic.

Transactions and Concurrency Control eBook

4 Comments on “How does the MySQL JDBC driver handle prepared statements

  1. Sounds like a good book, I’ll have to check it out. I use ColdFusion (on top of Java), and the query parameter-binding is presented as an abstraction in the ColdFusion languages. As such, I’ve never really thought too deeply about what it is actually doing at the server / database level. I only generally understood that it enhanced performance and implemented security.

    This is showing how big a blind-spot that shallow-understanding has given me. I’m also seeing now that, by default, the JDBC driver is only caching 25 prepared statements as well. Which, I don’t know, seems really small for a large application. Hopefully your book addresses questions like this.

    • My book covers database essentials, JDBC, JPA, and Hibernate. So, it’s surely useful even if you are currently using ColdFusion.

  2. This just blew my mind. For the last 15-years, I’ve just assumed that all query-plan caching with prepared statements took place on the server. I only just realized this is not happening because I was looking at the Prepared_stmt_count variable and it is always zero (0). I had no idea that query-plan caching could even be done on the client (as I didn’t think it would even make much of a performance difference).

    I am trying to understand how to take this information and update my internal mental model of prepared statements. Thanks for the insight!

    • This just blew my mind. For the last 15-years, I’ve just assumed that all query-plan caching with prepared statements took place on the server.

      Sound like you didn’t read my awesome High-Performance Java Persistence book. You’d have known all this, and even more, by now.

      MySQL doesn’t have an execution plan cache. On the server, it can only cache the Query Syntax Tree. On the client, it could cache the JDBC Metadata.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

High-Performance SQL Online Workshop - 27th of May