banner image 1 banner image 2

Why is MySQL skipping the index?

February 3, 2022
3 mins
blog-img 1
Rajesh krishnakumar

Slow queries, high Apdex score and average response time — this article solves all these and more!

By Rajesh krishnakumar — “Go above and beyond!”

All of us come through applications that use MySQL as RDBMS. MySQL came across in my life when I was in class 11.

It’s a wonderful RDBMS. MySQL is a fast, easy-to-use RDBMS being used for many small and big businesses.


Suddenly we saw in our observability platform a huge number of slow queries which made the Apdex score is worse than 0.7 and average response time are high as 3x of petrol price.

As per our observability platform indicates that MySQL query makes the application slow and 90 % of the bottleneck is DB only.

We fetch the slow query from the observability platform did explain in MySQL to see what is wrong, We saw MySQL scanning more rows and using where instead of index.

Process of solving the problem

First, We decided to do join decomposition. Split the query into two-part and did the load test not satisfy with response time it is just down by 10ms.

I and my manager started debugging the issue checking all indexes and other aspects of the problem. We know the bottleneck is in DB calls we need to improve that.

I accidentally added string value instead of int in the query when I ran a query in Production MySQL read replica and compared with a staging where value is int. There is a huge difference in query time taken.

In live DB we are getting good results, not in the stage, and asked my manager why there is so much difference in stage and live. After a lot of front and back.

My manager found out that it’s varchar vs int which I accidentally added in the live query, not in the staging query. BINGO

Here is a truth bomb

For comparisons of a string column with a number, MySQL cannot use an index on the column to look up the value quickly. If str_col is an indexed string column, the index cannot be used when performing the lookup in the following statement:

SELECT * FROM tbl_name WHERE str_col=1;

The reason for this is that there are many different strings that may convert to the value 1, such as '1', ' 1', or '1a'.


We solved the datatype mismatch issue and split the query.

After we deployed the changes to the production environment. Our avg response time is improved from 3x of petrol price to 500ml of packed drinking water.

We reduce our server also which can save an infra cost.

Improvement from ~300 ms to ~10 ms
Improvement from ~300 ms to ~10 ms
Feeling when we bring down the response time
Feeling when we bring down the response time


This is our journey to solve the MySQL query issue (string column with a number). Maybe it looks easy it’s just a data type mismatch issue, Searching for mismatch is a roller coaster ride. All the big problems have a small issue. Going forward we will add this (string column with a number query issue) in our checklist while debugging slow queries.



Meet the team!

Rajesh Krishnakumar

Muthukumar K

Mridula Saravanan

We at CaratLane are solving some of the most intriguing challenges to make our mark in the relatively uncharted omnichannel jewellery industry. If you are interested in tackling such obstacles, feel free to drop your updated resume/CV to!
blog-img 2


blog-img 3
5 mins
May 17, 2023
Sharing Data Between Controllers: Best Practices S...

This article will help you to understand the diffe

By Naveen C

blog-img 3
5 mins
March 21, 2023
Understanding Auto Layout and Constraints in Swift...

This article gives you an easy way of understandin

By Ramasamy P