I read that MySQL does not support server side query plan caching. So if i want to use PreparedStatements for performance benefits, what i can do is enable statement caching in JDBC Connection. So as per the docs it will enable caching of prepared statements on per connection basis.
What is the performance gain of PreparedStatement caching by JDBC connection compared to if MySQL had server side query plan caching ?
So if a PreparedStatement is indeed found in the physical connection's cache, does it mean that when it gets to mysql server, mysql would not run query optimizaton on it and will be able to directly execute it ?
Should i even use Statement caching at JDBC connection level while using MySQL as my database ? I am using Hikari database connection pool with Mysql JDBC connector.
解决方案
Yes, caching won't hurt if you know what you are doing. Combining a client side cache with a server side cache wilds performance benefits if you reuse prepared statements as they are supposed to (a lot of people forget about the most important part :D). Just set Connector/J properties properties correctly:
cachePrepStmts=true&useServerPrepStmts=true
While I'm not a big fan of micro benchmarks, here is one to back my statements up (lame pun indeed). The interesting part about the benchmark is that it shows that enabling server side caching may actually slow things down without some client side caching, but with both layers of cache enabled as well as the proper reuse of prepared statements you may actually get a good speed up.