All of us know that Database Indexes have a significant impact on improving query performance, but not everyone makes sure that the DB uses that index.

One of the SQL queries that were executed by one of our legacy apps was taking ~40 seconds to run on a MySQL instance.  That query was running on a table that contains more than 100M rows. After investigation, we found that there are many indexes out there that may lead the MySQL planner to make the wrong decision. After removing that redundant/unneeded index(es) we noticed that the query went from ~40 seconds to ~6 seconds.

Here is an example for the root cause:

Imagine that we have a table called test contains the following columns (post_id, user_id, status) and filled with ~100M row, and our business logic requires the following queries:

  • SELECT * FROM test where post_id in (10, 30, 50)
  • SELECT * FROM test where post_id in (10, 30, 50) and user_id = 283

Unfortunately, some engineers may create two indexes one for post_id and one for user_id but it should be one index covering the two columns.

Lessons Learned

  • Duplicate/Redundant Indexes hurts MySQL performance.
  • Having one index on multiple columns, ordering these columns from the most used to the least may offer better performance in all CRUD operations than one index per column.
  • Premature optimization is the root of all evil.
  • Use Explain statement as possible as you can, it helps you to understand what is going under the hood.
  • To find unused indexes in your database run the following query select * from sys.schema_unused_indexes.
  • To find redundant indexes, select * from sys.schema_redundant_indexes.

Useful MySQL Performance Tools

major/MySQLTuner-perl
MySQLTuner is a script written in Perl that will assist you with your MySQL configuration and make recommendations for increased performance and stability. - major/MySQLTuner-perl
majorGitHub
Percona Toolkit
Your enterprise DBAs oversee one of your most valuable assets: the database. They need to plan, design, and deploy new architectures to meet the needs of your applications.
Percona