Database Indexes Reference

Index types, when to create them, how to verify they are used, and common anti-patterns.

When to Index

Index columns that appear in:

  • WHERE clauses (filter conditions)

  • JOIN conditions (foreign keys)

  • ORDER BY clauses (sort operations)

  • GROUP BY clauses (aggregation keys)

Do not index columns on tables with fewer than ~1000 rows, columns with very low cardinality (like boolean active), or columns that are updated on every write.

Index Types

B-tree — the default, covers most cases
CREATE INDEX idx_devices_hostname ON devices (hostname);

B-tree indexes support equality (=), range (<, >, BETWEEN), prefix LIKE (LIKE 'sw-%'), and ORDER BY. They do not help with suffix matching (LIKE '%core').

Unique index — enforces uniqueness
CREATE UNIQUE INDEX idx_users_email ON users (email);

A unique index is both a constraint and a performance optimization. PRIMARY KEY implicitly creates a unique index.

Composite (multi-column) index — column order matters
CREATE INDEX idx_orders_customer_date ON orders (customer_id, created_at DESC);

A composite index on (A, B) supports queries filtering on A alone, or A AND B. It does not help queries filtering only on B. Think of it like a phone book: sorted by last name, then first name. You can look up by last name, or by last+first, but not by first name alone.

Partial index — index a subset of rows (PostgreSQL)
CREATE INDEX idx_orders_pending ON orders (created_at)
WHERE status = 'pending';

Partial indexes are smaller and faster when you frequently query a specific subset. A partial index on WHERE active = true avoids indexing the 90% of rows that are inactive.

Expression index — index a computed value (PostgreSQL)
CREATE INDEX idx_users_lower_email ON users (LOWER(email));

-- Now this query uses the index
SELECT * FROM users WHERE LOWER(email) = 'admin@example.com';
GIN index — for JSONB, arrays, full-text search (PostgreSQL)
CREATE INDEX idx_devices_config ON devices USING GIN (config);
CREATE INDEX idx_servers_tags ON servers USING GIN (tags);

GIN indexes support containment operators (@>, ?, ?|). Use for JSONB queries and array membership tests.

SQLite Indexes

SQLite index syntax
CREATE INDEX idx_devices_hostname ON devices (hostname);
CREATE UNIQUE INDEX idx_devices_mac ON devices (mac_address);

-- Partial index (SQLite 3.8+)
CREATE INDEX idx_active_devices ON devices (hostname) WHERE active = 1;
Check which indexes exist
sqlite3 myapp.db ".indexes devices"
sqlite3 myapp.db "SELECT * FROM sqlite_master WHERE type='index';"

MySQL Indexes

MySQL index syntax
CREATE INDEX idx_devices_hostname ON devices (hostname);

-- Prefix index for long strings (index first N characters)
CREATE INDEX idx_logs_message ON logs (message(100));

-- Fulltext index (MyISAM or InnoDB 5.6+)
CREATE FULLTEXT INDEX idx_docs_body ON documents (body);

MySQL uses the leftmost prefix of composite indexes. A FORCE INDEX hint can override the optimizer when it picks the wrong index.

Verifying Index Usage

PostgreSQL — EXPLAIN ANALYZE
EXPLAIN ANALYZE SELECT * FROM devices WHERE hostname = 'sw-core-01';

Look for Index Scan or Index Only Scan. If you see Seq Scan on a table with an index, the optimizer decided the index would not help (common for very small tables or low-selectivity queries).

SQLite — EXPLAIN QUERY PLAN
EXPLAIN QUERY PLAN SELECT * FROM devices WHERE hostname = 'sw-core-01';
-- SEARCH devices USING INDEX idx_devices_hostname (hostname=?)
MySQL — EXPLAIN
EXPLAIN SELECT * FROM devices WHERE hostname = 'sw-core-01';
-- Check the 'key' column for which index is used

Index Maintenance

List all indexes (PostgreSQL)
SELECT indexname, indexdef FROM pg_indexes WHERE tablename = 'devices';
Find unused indexes (PostgreSQL)
SELECT indexrelname, idx_scan, idx_tup_read
FROM pg_stat_user_indexes
WHERE idx_scan = 0
ORDER BY pg_relation_size(indexrelid) DESC;

Unused indexes waste disk space and slow down writes. Drop them after confirming they are not used by any query path.

Drop an index
DROP INDEX IF EXISTS idx_devices_hostname;

-- PostgreSQL: drop without blocking writes
DROP INDEX CONCURRENTLY IF EXISTS idx_devices_hostname;
Rebuild indexes (PostgreSQL)
REINDEX TABLE devices;
REINDEX INDEX idx_devices_hostname;

Covering Indexes

A covering index contains all columns the query needs. The database reads the index only, never touching the table.

PostgreSQL INCLUDE clause
CREATE INDEX idx_orders_covering ON orders (customer_id)
INCLUDE (total, status);

-- This query uses Index Only Scan -- no table access
SELECT total, status FROM orders WHERE customer_id = 42;

Anti-Patterns

  • Indexing every column — more indexes means slower INSERT/UPDATE/DELETE. Each write must update every index.

  • Composite index in wrong order — (created_at, customer_id) does not help WHERE customer_id = 42. Put the equality column first.

  • Indexing low-cardinality columns — an index on gender (3 values) rarely helps. The optimizer prefers a full scan.

  • Not analyzing after bulk load — the optimizer uses statistics. Run ANALYZE after loading large amounts of data.