loading...
Cover image for I've just enhanced performance from 21sec to 0.4sec with this one single change

I've just enhanced performance from 21sec to 0.4sec with this one single change

mkantz84 profile image Michael Kantz ・2 min read

This is the only thing that I've done to decrease my network call time dramatically.


TL;DR

SQL indexes.

OK, OK, I know what you think

Who doesn't know indexes are very important in all forms of SQL databases??
How come you ended up having a 21 seconds loader??

You're right

You're right, but it can be missed. I mean, we've missed it when we've started our MySQL architecture.
I always knew the role of indexes, but it's hard to notice the real importance of it when your SQL tables are small sized.

"Indexes are used to find rows with specific column values quickly. Without an index, MySQL must begin with the first row and then read through the entire table to find the relevant rows. The larger the table, the more this costs." 
How MySQL Uses Indexes

So as a Time To Market Startup, we just forgot to add some really important indexes to our tables.

How things escalated

In the beginning, there weren't any performance issues.
But when the data and the DB scaled up, then things started to slow down.
One day we've noticed that one network call is taking 21 seconds (!). 
That was the trigger to start digging into the code, trying to find performance issues.
Very quickly we found the time consuming source, and it was just one DB query. 
We also noticed that this query is joining two large tables, and none of the fields in the query where clause were indexed.

The solution

I added a single column index for each one of those fields.

ALTER TABLE `table_name`
    ADD INDEX `fieldName` (`fieldName`);
Enter fullscreen mode Exit fullscreen mode

WOW, the speed!
Suddenly the network call took 0.4 seconds. This change just blew our minds.


Conclusion

Do not underestimate the importance of SQL Indexes, and do not forget to add them to your SQL tables. 
One day you may end up with the query time of 21 seconds. It's not fun and it's embarrassing.

Discussion

pic
Editor guide
Collapse
eladsc profile image
eladsc

A great reminder we shouldn’t neglect the fundamentals

Collapse
edumqs profile image
edumqs

Indeed! I came across this problem once where the database became almost unusable. Took us a while to find such a simple thing.