This content originally appeared on DEV Community and was authored by Vish
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. 🚀
This content originally appeared on DEV Community and was authored by Vish
Vish | Sciencx (2024-10-17T03:10:07+00:00) (SQL Query) Caching vs Indexing in Express.js. Retrieved from https://www.scien.cx/2024/10/17/sql-query-caching-vs-indexing-in-express-js/
Please log in to upload a file.
There are no updates yet.
Click the Upload button above to add an update.