Why is MySQL skipping the index?
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.
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
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.
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.
Author
Rajesh Krishnakumar
Reviewer
Muthukumar K
Editor
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 careers@caratlane.com!
Leave a Reply