Database Indexing: Ace Your Next Tech Interview

You're in the interview. The senior engineer scribbles a SELECT * FROM users WHERE email = 'some@email.com'; on the whiteboard. "This query is slow, we're hitting millions of rows. How do you make it faster?" This isn't a trick question; it's a test of your foundational understanding of database indexing, something often overlooked in the rush to learn distributed systems or trendy frameworks. Your next tech interview, especially for backend or data roles at places like Google, Meta, or Stripe, will touch on this. Trust me, I've seen too many brilliant engineers stumble here, not because they don't know SQL, but because they don't grasp the "why" and "how" of indexing beyond just saying "add an index."

The "Why" is More Important Than the "How"

Anyone can type CREATE INDEX idx_email ON users (email);. A junior dev might even remember that. But a senior engineer explains why that works, and when it might not. Think of a physical phone book. Without an index, finding "John Smith" requires scanning every single page, name by name. In a database, that's a full table scan – a nightmare for performance on large tables. An index is like the alphabetical ordering in that phone book. You jump to 'S', then 'Sm', then 'Smi', drastically reducing the pages you need to flip through.

Specifically, most relational databases like PostgreSQL, MySQL, and SQL Server use B-trees for their primary indexing structure. A B-tree is a self-balancing tree data structure that maintains sorted data and allows searches, sequential access, insertions, and deletions in logarithmic time. That O(log N) search time is the magic. Without an index, you're looking at O(N) for a full table scan, a difference that becomes catastrophic as N (your table size) grows. When they ask about indexing, they're often probing your understanding of this fundamental trade-off.

Beyond the Basics: Types and Trade-offs

So, you established the "why." Now, let's talk types. Clustered vs. Non-Clustered indexes is a common follow-up. In SQL Server, a clustered index determines the physical order of data rows in the table itself. There can only be one per table, because, well, the data can only be physically sorted one way. Think of the dictionary: the words are sorted alphabetically, and the definition is right there with the word. That's a clustered index. Searching for a word is fast because the data you need is collocated.

A non-clustered index, on the other hand, doesn't reorder the physical data. It's a separate structure that contains the indexed column(s) and pointers (usually the primary key) back to the actual data rows. Imagine a book's index at the back. It tells you page numbers, but it doesn't physically rearrange the book's chapters. You can have multiple non-clustered indexes. PostgreSQL and MySQL's primary keys are typically clustered by default, but their secondary indexes are almost always non-clustered. This distinction is crucial. When you query a non-clustered index, the database finds the primary key(s) in the index, then performs a "bookmark lookup" to fetch the actual rows. That extra hop can add latency.

Here's where it gets interesting: index size. An index isn't free. It consumes disk space. For a table with 10 million rows, an index on a VARCHAR(255) column could easily be hundreds of megabytes, even gigabytes. More importantly, indexes slow down writes (INSERT, UPDATE, DELETE operations). When you modify data in an indexed column, the database has to update the original table and all relevant indexes. This is a classic "read performance vs. write performance" trade-off. If your application is write-heavy (e.g., an IoT ingestion service), too many indexes will kill your throughput. Don't just recommend an index without asking about the read/write ratio.

Covering Indexes and Multicolumn Indexes

You've impressed them with B-trees and clustered vs. non-clustered. Now, pull out the big guns: covering indexes. A covering index (also known as an index-only scan in PostgreSQL) is a non-clustered index that includes all the columns needed to satisfy a query. The database doesn't have to perform that "bookmark lookup" back to the main table. It can get all the data it needs directly from the index.

Consider SELECT name, email FROM users WHERE email = 'some@email.com';. If you only have an index on email, the database finds the email, gets the primary key, then goes to the main table to fetch name. If you create an index ON users (email, name), and both email and name are in the index, the database can satisfy the entire query from the index itself. This is incredibly fast. Most modern ORMs like Hibernate or SQLAlchemy won't automatically create these nuanced indexes; you need to define them explicitly in your schema migrations (ALTER TABLE users ADD INDEX idx_email_name (email, name);).

Then there are multicolumn (or composite) indexes. The order of columns in a multicolumn index matters a lot. An index on (last_name, first_name) is great for queries like WHERE last_name = 'Smith' or WHERE last_name = 'Smith' AND first_name = 'John'. It's not useful for WHERE first_name = 'John' by itself, because the index is sorted primarily by last_name. The database can't jump directly to "John" if it doesn't know the last_name. Always think about the leftmost prefix rule. This is a common gotcha.

Practical Scenarios: When and How to Apply

Let's ground this. Your team is seeing slow queries. Where do you start? Don't blindly add indexes. First, use your database's EXPLAIN (or EXPLAIN ANALYZE in PostgreSQL) command. This shows you the query execution plan. It tells you if it's doing a full table scan, an index scan, or an index-only scan. It highlights bottlenecks. This is your most powerful debugging tool.

You'll often find indexes beneficial for:

• WHERE clauses: Any column frequently used in equality (=), range (>, <, BETWEEN), or IN conditions.
• JOIN conditions: Columns used in ON clauses for joining tables.
• ORDER BY clauses: An index on the ordered columns can avoid a costly sort operation.
• GROUP BY clauses: Similar to ORDER BY, an index can speed up aggregation by pre-sorting the data.

What about LIKE '%foo'? An index usually won't help here because the leading wildcard (%) prevents the database from using the sorted order. It still has to scan. For full-text search, you'd look at specialized solutions like PostgreSQL's tsvector/tsquery or dedicated search engines like Elasticsearch.

Also, be mindful of low-cardinality columns (e.g., a boolean column is_active). An index on is_active might not be very effective because there are only two possible values. The database might decide a full table scan is faster than navigating the index just to find half the rows. It depends on the data distribution and table size, but it's a good heuristic.

Finally, think about partial indexes (PostgreSQL, SQL Server). These only index a subset of rows in a table. For example, CREATE INDEX idx_active_users_email ON users (email) WHERE is_active = TRUE;. This is perfect if you mostly query active users and want to keep your index smaller and faster to maintain. It's a powerful optimization that shows a deep understanding of database internals and performance tuning. Don't just regurgitate definitions; apply them to realistic problems.

Ready to Ace Your Next Interview?

Practice with AI-powered mock interviews tailored to your target role and company. Start Practicing for Free | Explore Interview Prep