Hello Devs, This is my first post on this platform! 👋
I wanted to share a surprising experience I had with Express.js and SQL. I’m a beginner developer, and while working on the API for my project, I was handling over 200k+ API requests per day. Initially, I set up an SQLite database (~400 MB) with an Express.js API, and used Node.js caching to speed things up.
At first, the response time for a query was around 200-300 ms, since it involved several JOINs and searches. So, I decided to run some experiments to see if I could improve performance.
Here’s what I did:
- I removed Node caching.
- I created indexes on 20+ columns across 5+ tables (my DB has a total of 103 columns in 5 tables).
The result was surprising! After indexing, some of my more complex queries, involving multiple JOINs, were being executed in just 3-5 ms.
Previously, with Node caching:
- The first request used to take around 300-400 ms.
- Subsequent requests would take 2-5 ms (due to caching).
After, with only SQLite indexes:
every requests only takes 7-10 ms without any caching.
Now the queries are consistently fast, and this also reduced the server load! Initially, I was running my app on a DigitalOcean droplet with 4 GB of RAM and 4 vCPUs. But after optimizing the SQL queries, I can comfortably handle the same workload with just 2 GB of RAM and 2 vCPUs.
I know this may sound simple, but proper SQL indexing made such a big difference in query performance that I no longer even need to rely on caching. 🚀
Top comments (0)