What Are Database Indexes? How AI Coders Can Speed Up Slow Queries

Why AI Coders Need to Understand Database Indexes

Here's what happens in every AI-built app without proper indexes:

1. You build the app. It works great with 100 test records.
2. Real users start using it. The database grows to 10,000+ records.
3. Queries start taking 500ms, then 2 seconds, then 10 seconds.
4. Users complain the app is slow or abandon it entirely.

The problem isn't your code — it's that the database is doing a full table scan on every query, reading every single row to find the few that match. An index prevents this by creating a lookup structure, like a phone book that's already sorted alphabetically.

AI almost never adds indexes to the schemas it generates (unless you ask), and it's one of the highest-impact performance improvements you can make — often turning a 5-second query into a 5-millisecond query with a single line of SQL.

Real Scenario: A User Search Feature

Without indexes, every search scans all 500,000 rows — even though the result might be 1 user. Let's see what happens.

What AI Generated

-- The users table AI creates (no indexes beyond primary key)
-- Tested with PostgreSQL 16

CREATE TABLE users (
  id          SERIAL PRIMARY KEY,    -- Auto-indexed (primary keys always are)
  email       VARCHAR(255) NOT NULL,
  name        VARCHAR(255),
  status      VARCHAR(50) DEFAULT 'trial',  -- 'active', 'cancelled', 'trial'
  plan        VARCHAR(50),
  created_at  TIMESTAMP DEFAULT NOW(),
  last_login  TIMESTAMP
);

-- The queries the app runs:

-- Search by email (exact match)
SELECT * FROM users WHERE email = 'chuck@example.com';
-- Without index: scans 500,000 rows → ~200ms
-- With index: finds it in ~0.1ms

-- Filter by status
SELECT * FROM users WHERE status = 'active' ORDER BY created_at DESC;
-- Without index: scans 500,000 rows, sorts in memory → ~800ms
-- With index: reads sorted subset directly → ~5ms

-- Filter by status AND sort by date
SELECT * FROM users WHERE status = 'active' ORDER BY created_at DESC LIMIT 20;
-- Without composite index: scan + sort → ~500ms
-- With composite index on (status, created_at): → ~1ms

Adding the Indexes

-- Index for email lookups (unique because emails should be unique)
CREATE UNIQUE INDEX idx_users_email ON users(email);

-- Index for status filtering
CREATE INDEX idx_users_status ON users(status);

-- Composite index for status + date sorting (most powerful)
CREATE INDEX idx_users_status_created ON users(status, created_at DESC);

-- Index for login tracking
CREATE INDEX idx_users_last_login ON users(last_login);

In Prisma (ORM)

// schema.prisma — adding indexes with Prisma
model User {
  id        Int      @id @default(autoincrement())
  email     String   @unique  // @unique automatically creates an index
  name      String?
  status    String   @default("trial")
  plan      String?
  createdAt DateTime @default(now())
  lastLogin DateTime?

  // Explicit indexes
  @@index([status])                           // Single column index
  @@index([status, createdAt(sort: Desc)])    // Composite index
  @@index([lastLogin])                        // For login tracking queries
}

Understanding How Indexes Work

The Textbook Analogy

Imagine a 500-page textbook about JavaScript.

• Without an index: To find information about "closures," you read every page from 1 to 500 until you find it. This is a full table scan.
• With an index: You flip to the index at the back, find "closures → page 142," and go directly there. The database does exactly this.

What the Database Actually Creates

When you create an index, the database builds a B-tree (balanced tree) data structure. Think of it as a sorted, hierarchical lookup table:

-- Conceptual B-tree for idx_users_email:
--
--                    [M]
--                   /   \
--              [D-G]     [P-T]
--             /  |  \   /  |  \
--          [A-C][E-F][H-L][N-O][Q-S][U-Z]
--
-- Looking up "chuck@example.com":
-- Start at [M] → "C" < "M" → go left
-- [D-G] → "C" < "D" → go left
-- [A-C] → found "chuck@example.com" → row ID: 42,157
--
-- 3 steps instead of 500,000. That's the power of indexes.

For a table with 1 million rows, a B-tree index needs about 20 comparisons to find any record. Without the index: 1 million comparisons. That's why indexes make such a dramatic difference.

Types of Indexes

Composite Indexes: Column Order Matters

A composite index on (status, created_at) is NOT the same as one on (created_at, status). The index works left-to-right:

CREATE INDEX idx_status_date ON users(status, created_at);

-- ✅ Uses the index (starts with leftmost column):
SELECT * FROM users WHERE status = 'active';
SELECT * FROM users WHERE status = 'active' AND created_at > '2026-01-01';
SELECT * FROM users WHERE status = 'active' ORDER BY created_at DESC;

-- ❌ Does NOT use the index (skips the leftmost column):
SELECT * FROM users WHERE created_at > '2026-01-01';
SELECT * FROM users ORDER BY created_at DESC;

Rule: put the column you filter by most often first in a composite index.

The Trade-Off: Reads vs. Writes

Indexes aren't free. Every time you INSERT, UPDATE, or DELETE a row, the database must also update every index on that table. More indexes = slower writes.

For most web applications (read-heavy), the trade-off is overwhelmingly in favor of indexes. A typical web app does 10–100x more reads than writes.

Where indexes hurt: write-heavy scenarios like logging systems, analytics ingestion, or IoT data streams where you're inserting thousands of rows per second and rarely querying.

How to Check If Your Queries Use Indexes

-- PostgreSQL: Use EXPLAIN ANALYZE to see the query plan
EXPLAIN ANALYZE SELECT * FROM users WHERE email = 'chuck@example.com';

-- WITHOUT index, you'll see:
-- Seq Scan on users  (cost=0.00..12345.00 rows=1 width=128)
--   Filter: (email = 'chuck@example.com')
--   Rows Removed by Filter: 499999
--   Planning Time: 0.1ms
--   Execution Time: 198.5ms        ← SLOW

-- WITH index, you'll see:
-- Index Scan using idx_users_email on users  (cost=0.42..8.44 rows=1 width=128)
--   Index Cond: (email = 'chuck@example.com')
--   Planning Time: 0.1ms
--   Execution Time: 0.05ms         ← FAST

Look for "Seq Scan" (bad — scanning every row) vs. "Index Scan" (good — using the index). If you see Seq Scan on a large table with a WHERE clause, you probably need an index.

What AI Gets Wrong About Database Indexes

1. Not Adding Any Indexes

AI generates schemas with tables and columns but rarely adds indexes beyond the primary key. Everything works in development (100 rows). Everything fails in production (100,000 rows).

Fix: After AI generates a schema, ask: "What indexes should I add for the queries this application will run? Consider the WHERE clauses, JOINs, and ORDER BY columns."

2. Indexing Every Column

When you ask for indexes, AI sometimes goes the other direction and indexes every column. This wastes disk space and slows down every write operation.

Fix: Only index columns that appear in WHERE, JOIN ON, ORDER BY, and GROUP BY clauses. If a column is only used in SELECT (display), it doesn't need an index.

3. Missing Composite Indexes

AI adds separate indexes on status and created_at, but the query filters by both: WHERE status = 'active' ORDER BY created_at. Two single-column indexes are much less efficient than one composite index for this query.

-- AI generates (suboptimal):
CREATE INDEX idx_status ON users(status);
CREATE INDEX idx_created ON users(created_at);

-- Better for this query pattern:
CREATE INDEX idx_status_created ON users(status, created_at DESC);

4. Forgetting Foreign Key Indexes

In PostgreSQL, foreign keys are NOT automatically indexed (unlike MySQL InnoDB). If you have posts.author_id referencing users.id, the JOIN query needs an index on author_id. AI frequently misses this.

-- Add this for every foreign key in PostgreSQL:
CREATE INDEX idx_posts_author_id ON posts(author_id);

How to Debug Slow Queries with AI

Index Decision Checklist

• ✅ Index columns used in WHERE clauses (especially exact matches)
• ✅ Index foreign key columns (for JOINs)
• ✅ Index columns used in ORDER BY (especially with LIMIT)
• ✅ Use UNIQUE index when the column should have unique values
• ✅ Use composite index when queries filter on multiple columns together
• ⚠️ Don't index columns only used in SELECT (display-only)
• ⚠️ Don't index boolean columns with only two values (low cardinality)
• ⚠️ Don't index tables with fewer than 1,000 rows (full scan is fine)
• ⚠️ Don't add more than 5-7 indexes per table without measuring impact

What to Learn Next

Frequently Asked Questions