This is the only thing that I've done to decrease my network call time dramatically.
TL;DR
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`);
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.
Top comments (2)
A great reminder we shouldn’t neglect the fundamentals
Indeed! I came across this problem once where the database became almost unusable. Took us a while to find such a simple thing.