SelectStar graph of PostgreSQL queries in the last hour

3 Ways to Optimize MySQL Performance: Indexing

Companies are trending towards using more and more open-source databases in production because of the number of benefits that they provide. Not only are they more cost effective, but they tend to be more flexible and stable than traditional database platforms. The ability to tailor the platform to specific internal needs serves as a big driver for many organizations to leverage open-source databases.

One of the most common is MySQL — in fact, current numbers from DB Engines show that it’s the second most popular database management system behind Oracle. Some of the world’s biggest web-based companies — like Facebook, YouTube and Twitter — use MySQL.

This marks the first in a three-part series where I’ll share how you can drive better performance from your MySQL database to optimize the flexibility and stability of your environment.

INDEXING 101

You may have heard or even used indexing. Most of us have, but few realize how important good indexing really is. If you’re not familiar with it, indexing creates a data structure that allows you to use binary searches across your environment. The data structure incorporates the field value and the associated record.

For all the good that indexing brings to your system, too many indexes or poorly written indexes can quickly fill up your file system and cause write operations to actually take more time. However, when written correctly indexing can reduce the number of disk reads tremendously. This decreases the time of your reads and allows your database to run much more efficiently.

In addition, indexing can help improve the performance of your search queries because it reduces the amount of data that needs to be examined when you initiate a search. In today’s fast-paced world, faster access to your data (without overloading your system) serves as a key component for optimizing performance.

TIPS FOR IMPROVING YOUR INDEXING

  • Avoid duplication. Duplication takes up more disk space, and for no good reason. Set parameters on your indexing to make sure you don’t have multiple indexes for the same query — it not only takes up more space, but it can also slow down your environment.
  • Use the right type of index. B-trees are the most commonly used for index, due to the fact that they are the most efficient. However, in certain cases, other types of indexes may be more useful for your MySQL database, like a hash table index (best for searching values) or an R-tree data structure (recommended for spatial searches).
  • Review your indexes regularly. What are the most common queries on your database? Do you have indexes for these? See how they are being leveraged and make sure you have the right ones in place to make sure you are getting the best bang for your buck.

HOW TO MONITOR INDEXING

Query execution time serves as the best way to monitor your indexing to make sure it’s optimizing performance — and not dragging down your environment. Having the ability to dig deeper into how your queries are performing can help you drill down to specific indexes that may not be performing the way you intended.

Using a SaaS database monitoring platform with query-level monitoring can give you visibility into your index performance. In addition, you can monitor indexing through primary and foreign keys as well.

SelectStar Screenshot

Figure 1: Dashboard monitoring query execution time in SelectStar

With visibility into the performance of your indexes, you can immediately drill down to the root-cause of the issue instead of hunting through your indexing — which could potentially take more time than you can spare — so that you can focus your energy where it’s most important in your production environment.

Interested in trying a SaaS database monitoring platform to evaluate MySQL performance? Try a free trial of SelectStar to track your MySQL indexes through query-level monitoring to drive better performance across your environment.

LET'S GET STARTED!

Try SelectStar for 14 days. No credit card required.

TRY IT NOW!