Simpler, Faster, Stronger: MySQL Optimization Tips
Faster, Better, Stronger!
Usually an article like this one will start out with the technical word "scaling". Unfortunately like health care reform, everyone can't always agree on what they mean by it, or even what the goal is. So, I deliberately chose not to use that word, and opted for non-technical words that we can all agree on. Typically, when our database is slowing down, we want it to be faster, stronger, bigger and better!
With that in mind I'm going to discuss some of the various ways to get there, and hopefully put some of the technology options in perspective. This will help you survey the landscape, and plan for your future needs. This first part of the article will discuss query tuning and hardware changes, while the second part will discuss adding additional servers, and application changes to make that work.
I've been independent consulting for over twelve years now. Over that time, the most common trouble spot has been errant SQL. SQL is the language you use to communicate with the database, but it is also the frame with which you lay down your problem, and feed it to a computer for solving.
Simplify the SQL
Sometimes, in the rush to get something out the door, we write SQL, which is asking for more than it needs. Perhaps we need two rows, but we ask for the whole table. During development, the table had only ten rows, so the code was very fast. As it's rolled out in production, we find it getting slower and slower. Turns out this little bit of code is on the main page of the website, and new users to the site all hit it. Now the table has ten million rows, and it is indeed a showstopper. We fire up our monitoring tools, identify this query as a very slow query, a repeat offender. Adjusting the SQL to read just the two rows our application wants, it still delivers the application what it needs, but now taxes the database 1/5millionth as much. Now that's a big savings, and now the database has time to do all the other things its users are asking of it, and still has time to rest besides!
Add indexes or adjust tables
Imagine you're trying to find 230 Fifth avenue in Manhattan, but the addresses are in random order. You'd have to start at the beginning of Fifth avenue, and go to every single building to see if it was the address you're looking for. This would take hours or days *and* be a lot of work besides. You'd be tired out! That's what happens to our database if we don't index our data correctly. The query will instruct the database to find a record (like our address above) but won't have an easy path to find it. Identifying these queries and fixing them can speed up those queries dramatically, freeing the database to do lots of other work!