Our technical team doesn’t only fix your issues and optimize your servers, they’re all about sharing knowledge. This is the second article on our blog that was written by one of our expert technicians. In this article, Kevin Quinn shows you how you can boost MySQL efficiency by adding an index.
MySQL tuning and optimization is a bit of an art form. There is no easy button for MySQL tuning. Every server and every application is unique and has different requirements. This article does not aim to go into the many facets of MySQL Performance Tuning. In this article, we will examine something that’s often overlooked when admins and developers attempt to improve performance — indexes.
What’s an index?
An index is a data structure stored on disk just like a standard mysql table. You can think of it just like the index in the back of a book. It creates a relationship between columns in a similar way. Let’s take a look at a very simple example in action and how it can speed up query execution plans.
First we’ll create a table called citizens with a social security number, and a name.
We populate 81920 rows via simple php script, with randomized “names”, and ss_nums up to 81920. This will be our sample data.
So let’s get a baseline. Use SQL_NO_CACHE to prevent mysql caching on this query. Also, we are selecting NOW() to invalidate cache storage, as mysql will refuse to cache NOW() SELECTS. This is just to make sure results are not skewed from mysql’s caching.
This took a little over a tenth of a second. Doesn’t seem long, but multiply the amount of rows times 10, and you have a significant slowdown. Also if there are a constant stream of these queries, you could have a bottleneck quickly.
So now let’s create an index on name and see what happens.
Wow! A full tenth of a second faster. Let’s see what mysql’s EXPLAIN shows for each.
We see there are only 3150 rows required with the index. We should have expected this. Let’s remove the index and try again.
Notice that mysql’s optimizer has to resort to a full table scan here. In this simple example, it was much slower without the index.
So are indexes the solution to every MySQL query? No. But they can be a simple solution to dramatically increase query performance. There are other factors to consider when profiling your application to increase efficiency. Keep in mind that indexes will slow down UPDATES and INSERTS. So if your table is write heavy and light on reads, this approach will likely not be the right one for you.
We encourage you to give this a try. But, if you’d like some help optimizing your server, our technicians are always ready and willing to lend a hand. Just give us a shout.
For more information on MySQL, we recommend going straight to the source: