Optimizing MySQL Queries with Spring’s JdbcTemplate

Optimizing MySQL Queries with Spring’s JdbcTemplateLast week I migrated Stack Hunter’s back-end from MongoDB to MySQL.  (There was no problem MongoDB, it’s just easier for most users to connect to their existing MySQL database than install something new.)  In the process I spent some time optimizing the new database queries and indexes to make things snappy.  I started by loading up 100,000 exceptions into the database, then went to work.

 

 

Query Execution Plan

MySQL has a great tool for understanding your queries.  Just prefix your SQL statement with “EXPLAIN” and it spits out a bunch of useful information.

MySQL Workbench Editor

EXPLAIN tells you which indexes MySQL considered for your query, which index it choose if any, whether it had to use a temporary table, and so on.  You can get a complete list and description of the output fields in MySQL’s Reference Manual (https://dev.mysql.com/doc/refman/5.5/en/explain-output.html).  You can also check out this old, but informative slide on the topic (http://www.slideshare.net/phpcodemonkey/mysql-explain-explained).

MySQL Workbench

One way to use EXPLAIN is by adding it to your queries in MySQL Workbench’s SQL editor.  This approach works very well for running quick tests or if you have a small number of queries.  In my case Stack Hunter has a couple dozen select statements implemented as JDBC prepared statements.

If I was going to use EXPLAIN, I’d have to copy the queries from my Java classes to the SQL editor, replace the question mark placeholders with literal values, then hope I didn’t screw anything up.  As you can see, the workbench approach doesn’t work well for a large set of prepared statements.  It’s just too manual and error prone.  It’s also not something that can be reused easily with new queries or on future projects.

Instrumenting JdbcTemplate

Unless you’re doing something fairly specialized, no one should be using the JDBC APIs on their own today.  At a minimum, you should be using a framework like Spring’s JdbcTemplate that handles all the boilerplate resource management code.

Since all of Stack Hunter’s SQL statements run through JdbcTemplate, that’s a natural place to call EXPLAIN on slow running queries.  By wrapping JdbcOperations (JdbcTemplate’s interface) with a dynamic proxy, we’re able to intercept each query along with any arguments.

The wrapper allowed me to run the original query unchanged, then run it again with the “EXPLAIN ” prefix using the supplied arguments.  Initially I ran EXPLAIN on all queries, but later I set it to only run for statements that took more than 10 milliseconds.  This conditional execution is hard-coded, but you can make it configurable in your own app.

Explaining Queries

With the hooks in place, the explain method retrieves each query’s execution plan and logs it for later analysis.  I prefer outputting tab-separated-values (TSV) since they can be pasted into Excel without any extra work.

Once I had the data in Excel it wasn’t hard to see where Stack Hunter needed indexes or improved queries.  Fortunately, no structural changes were needed, but that could also have been an option.

Your database is going to be different, but keep any eye out for these basic optimization candidates.

FieldValueMeaning
KeyNullNo appropriate index was found to speed up your query.
ExtraUsing temporaryA temporary table was created to hold your results.
ExtraUsing filesortSorting could not be done using the index alone.

Joins are also a good place to improve performance.  Coming from a NoSQL, document database, Stack Hunter’s queries were all simple, no-join selects.  If you are using joins, you’ll want to run them all through EXPLAIN.

The Final Results

Outside of the text-based searches, all queries are now running in the tens of milliseconds or less on 100k exceptions.  This is more than adequate for the downloadable version of Stack Hunter.  Of course, I’ll need to test into the millions for the SaaS version, but that’s for another time.

 

 

About Dele Taylor

Dele Taylor is the founder of StackHunter.com -- a tool to track Java exceptions. You can follow him on Twitter, G+, and LinkedIn.

3 Responses to “Optimizing MySQL Queries with Spring’s JdbcTemplate”

  1. Good guide, Dele. I have to admit that one of my main web projects definitely need MySQL optimization, I got a feeling that this will lead to huge speed improvement.

  2. Thanks and glad I could help 🙂

Trackbacks/Pingbacks

  1. StackHunter Beta 1.1 Released - August 26, 2014

    […] love MySQL, but had to replace it with the H2 Database to make the setup easier.  The transition was a fairly […]