(SQL Query) Caching vs Indexing in Express.js

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. 🚀