DATABASES GUIDE
MySQL Indexing and Query Optimization for Node.js APIs
By the HireReadyAI team · Last updated 6 October 2026 · 9 min read
Covering indexes, EXPLAIN, N+1 queries and connection pooling from the Node side.
Why indexes matter from Node
Node APIs often look fast in development with tiny tables and fail in production when a filter does a full scan. Indexing is not only a DBA topic — API authors choose the WHERE, JOIN, and ORDER BY clauses that indexes must support.
Choosing columns
Index columns used for equality lookups, joins, and high-selectivity filters. Composite indexes should follow the leftmost prefix rule: an index on (user_id, created_at) helps filter by user_id, or user_id plus created_at range, but not created_at alone.
Using EXPLAIN
Run EXPLAIN on slow endpoints. Look for type=ALL (full scan), high rows estimates, and filesort. Confirm key is the index you expect. In interviews, walk through reading an EXPLAIN row rather than saying 'I would add an index' with no evidence.
N+1 queries from Node
N+1 happens when you query a list, then query once per row inside a loop. Fix with JOINs, IN-list batching, or DataLoader-style batching. This often shows up in ORMs. Measure query count per request in staging.
Covering indexes
A covering index includes all columns a query needs, so MySQL can answer from the index alone. Useful for hot list endpoints. Do not invent huge indexes for every query — each index slows writes and uses memory.
Pool and query work together
Slow queries hold pool connections longer, which looks like a Node pool problem. Optimize the query and index first, set timeouts, then size the pool. Mention both sides in an interview to show production judgment.