Optimizing Queries with Explain Plans

In this lesson, you will learn how to use database "Explain Plans" to look under the hood of your queries and identify performance bottlenecks.

When you run a SQL query, your database engine doesn't just execute it blindly; it creates an execution plan—a roadmap describing exactly how it will retrieve the requested data. In Node.js, we often assume our queries are efficient until they start slowing down our application. By prefixing a query with EXPLAIN, you can force the database to reveal this roadmap.

Reading the Roadmap

When you run EXPLAIN SELECT * FROM users WHERE email = 'test@example.com';, the database returns metadata about the operation. You should look for two critical indicators:

1. Type of Scan: Look for type: ALL. This is a red flag, meaning the database is performing a "Full Table Scan"—reading every single row on disk because it can't find an index to use. Ideally, you want to see const, eq_ref, or ref.
2. Rows: This tells you how many rows the database estimates it must examine to find your result. If you are querying for a single user by ID, but the rows value is in the thousands, your query is likely not using an index efficiently.

Example in Node.js

If you are using a library like pg or mysql2, you can execute the explain plan as a standard query:

const query = 'EXPLAIN SELECT * FROM orders WHERE user_id = $1';
const result = await client.query(query, [userId]);

console.table(result.rows);

By inspecting the output, you might discover that a specific column isn't indexed, forcing the database to work much harder than necessary. Once you add an index to that column, running EXPLAIN again will show the type changing from ALL to ref or const, and the rows count dropping significantly.

Think of an Explain Plan as a diagnostic tool. Instead of guessing why an endpoint is slow, the database tells you exactly where the "Disk I/O" cost is being spent, allowing you to optimize your schema or your query structure with precision.

Key Takeaway: Use the EXPLAIN keyword to verify that your database is using indexes effectively, turning slow, full-table scans into fast, targeted lookups.