Database Indexing: How we increased our overall database performance

5 min readDec 24, 2023

Recently, I ran into some database performance issues at work and thought I would share how my coworker (Daniel Fanara, search his publication using @dfanara) and I resolved it. Hopefully you find this as useful as I did 😃.

Disclaimer: I do use some actual screenshots from work but they are just showing results from our exploration and do not contain any sensitive information.

So, let’s go over some prerequisite information.

Database Indexes: What are they? What do they do?

The best way to think of database indexes is the the same way you think of indexes in a book. Imagine you are looking for a specific word in this imaginary book like the word python. Instead of going through every single page to find where the word is mentioned the index will tell you exactly where to go, saving you tons of time. Well, database indexes do the same thing for your queries.

There are a few types of database indexes that I want to cover with you as well:

BTREE, Balanced Tree

This is the default index for most relational database, this is because it is meant to handle indexing for the most common queries that occur.
This is great for filtering data with a range of dates, doing equality/specific values, sorting resulting in ASC or DESC order, and setting indexes for joined tables that share specific columns

RTREE

Specifically used within databases that store multidimensional like spatial data.
This can be in 2,3 and even 4 dimensions(time being the fourth)

FULL TEXT

This index is great for searching large pieces of text like blog posts.

There are a few things that happen to accomplish this:

Tokens: breaking down the full text into individual words or tokens
Stemming: reduce the words down to their root form so that it can be searched more efficiently
Stopwords: common words like the , a , and etc. get ignored in the index

HASH

This is extremely efficient with searching for exact matches
This used a hash table structure and maps keys to specific locations within the index

Now Let’s Talk About the Database Issues

So, now that we have gone through the index overview, let’s talk about the issues we were having with database at work.

We use MySQL as our production database for one of our products which connects to a Wordpress instance as our CMS and then we display that on our application frontend using NextJS

What happened?

We received some alerts that our database CPU usage was peaking at 100%, the ops/seconds plummeted and then we noticed that the reads using index/total_reads percentage was only around 30–40%.

Our site had a traffic spike at the same time as a deployment
A recent Wordpress update had an addition impact on performance

Investigation:

The main way we investigated the performance issues was by utilizing the performance schema in MySQL (there is a performance schema in most, if not all modern SQL databases, ex. PostgreSQL).

Once we looked into the performance_schema.event_statements table we found our biggest problem and the topic of this article:

We were doing full table scans on our top most used queries to return single digit rows, which is extremely inefficient 🤦🏽‍♂️.

Next…. we used EXPLAIN statements to dig further into the queries being ran and this is what we found:

out of 62,674 rows that were evaluated in one of our queries only 19.66% of the rows returned were returned for the WHERE clause, the other 80.33% were filtered out
again, extremely inefficient

Solution:

So, after doing a little bit of investigation we found all of these places where we were ineffectively querying the database.

We ended up creating BTREE and HASH indexes on the queries that needed it and afterwards we saw a massive improvement in row filtration and overall performance.

BTREE index creation example:

HASH index creation example:

One of our biggest successes:

Before:

out of 290,290 rows that were evaluated in this query only 10% of the rows returned were returned for the WHERE clause, the other 90% were filtered out

After:

Same query as above but way more performant

only one row is evaluated
Wayyyy more efficient and and helps with the reduction of query response time and CPU utilization.

Conclusion

This project wasn’t really planned we just saw the problem hit one day and just decided to quickly investigate and come up with a solution. All in all it took us about 1.5–2 days of collaborating and fixing the issue for an insane amount of performance gain.

I do want to specify that this is on a small scale. Once you get into larger and more complex systems it will require more lift to implement something similar to what we did here.