All 15 Databases questions
More in Databases
What is the N+1 query problem, and how does it commonly arise in a Node.js application using an ORM? What is a database seed script, and how does it differ from a migration? What is the difference between a one-to-many and a many-to-many relationship in a relational database, and how is each modeled? What is a database transaction, and how would you implement one in Node.js to ensure multiple related writes either all succeed or all fail together? What is database replication, and how does a Node.js application typically route reads to replicas while sending writes to a primary? What is optimistic concurrency control, and how would you implement it to prevent lost updates when two users edit the same record simultaneously? What is database sharding, and what problem does it solve that replication alone doesn't address? What is a database connection leak in a Node.js application, and how does it typically happen when using a connection pool? What is a database deadlock, and how might it occur between two concurrent transactions in a Node.js application?
Advertisement
Node.js › Databases

What is database indexing, and how does an index improve query performance in a Node.js application's database layer?

Advanced

Answer

An index is a separate data structure (commonly a B-tree) that the database maintains alongside a table, allowing it to look up rows matching a specific column value without scanning every row in the table sequentially -- dramatically speeding up queries filtering or sorting on indexed columns, at the cost of additional storage space and slightly slower write performance, since every index must also be updated on every insert or update.
-- Without an index, this query scans the entire orders table
SELECT * FROM orders WHERE customer_id = 12345;

-- Creating an index dramatically speeds up that exact query pattern
CREATE INDEX idx_orders_customer_id ON orders(customer_id);
Real-world example An orders table growing to millions of rows starts causing a 'get customer's orders' query to take several seconds; adding an index on the customer_id column reduces that same query to milliseconds by letting the database jump directly to matching rows instead of scanning the entire table.

Common follow-ups: How do you identify which columns actually need an index versus adding indexes speculatively everywhere?;What's the tradeoff of having too many indexes on a frequently-written-to table?

Performance Optimization & Profiling;SQL Queries
All 15 Databases questions