About two years ago, Jumpchart was already a bigish app (for us) and Staction was growing fast after its release. We realized we would need to do something to increase performance so that our users wouldn’t feel our growing pains.

Besides the easy approach of just getting faster hardware, we also decided to dive into database tweaks – and couldn’t have been happier about the results. Or more surprised.

Like many (probably most) applications for the Web, all of our apps run on MySQL. And while we’re old schoolers at it, we never really worried much about how fast each SQL query was. When your app is small, it hardly matters if you have poorly written queries or inefficient table indexes. Once you start getting the first thousands of daily hits, bad code comes back to bite you. Hard.

The first step we took was to develop code that would tell us exactly which queries weren’t performing well. By adding some timers to our OO database handler, we easily had after the first few days a huge log of all queries that were performing poorly in our MySQL server. It’s really a great way to keep track of how database performance evolves in your app.

The next step was to actually improve things, and we started that by reading O’Reilly’s High Performance MySQL, which is the best book to get you started on all things database performance – the authors have a nice blog also. It walks you through the very basics and low-level aspects of each storage engine and the internal query parser, and then shows you multiple ways of improving performance.

Table indexes are probably the most important aspect of performance in a MySQL server, and also the easiest to actually implement. It’s nowhere near the complexity of replication or load balancing, but it can give you amazing performance improvements. Here’s a good example of a query quite common that we use in Jumpchart.

SELECT * FROM pages WHERE parent_id=428230

Running it without any use of indexes, our server takes 6 seconds to return the row we’re looking for. Given that each page load requires multiple queries to be properly built, you can imagine how unusable the app would be with performance like that.

Now’s the interesting thing. By indexing the parent_id column in the pages table, the server runs the exact same query in 0 seconds; that’s right, it doesn’t even blink to fetch the results!

This happens because an index in MySQL works just like the one from a book; it lets the database go straight to the “page” it needs to go instead of flipping through all pages until it finds what it’s looking for. The 6 seconds the initial query took was basically due to MySQL going through 400 thousand rows until it found what we asked it to find. In the second approach, MySQL just looks at the index and knows exactly where to go.

We’ve altered dozens of tables within our app this way. Not every adjustment is as dramatic as the one above, and most are more complex. Sometimes you get a few milliseconds, sometimes you win several seconds, but every little bit helps.

If you’re an app developer, you should really find some time to research the ins and outs of MySQL performance and what affects it. Besides the joy of making your app run faster, the process you’ll go through can be very fun, surprising and inspiring. Not to mention that you can save money by getting the same hardware to do more for you.