Database Indexing Mistakes That Are Quietly Killing Your App's Performance

Adding indexes to columns, even those used in WHERE clauses, can slow down writes like inserts, updates, and deletes by creating multiple copies of the data. Instead of indexing every column, focus on indexing columns based on actual query patterns. Use tools like EXPLAIN in Postgres and MySQL to see which columns are scanned and create indexes that match those specific access patterns.

When creating composite indexes, pay attention to the order of the columns. A composite index on (user_id, created_at) is only useful if queries typically filter by user_id first and then by created_at. Queries that filter by created_at alone won't benefit from this composite index.

Foreign keys are often overlooked when it comes to indexing. Without indexes on foreign key columns, joins and related queries can lead to full table scans, causing performance issues as the related tables grow.

Adding an index doesn't always guarantee that the database will use it. Mismatches in data types, wrapping indexed columns in functions, or using leading wildcards in LIKE queries can prevent an index from being used even if it exists. To confirm that the index you added is being utilized, run EXPLAIN ANALYZE on important queries.

Not all performance problems can be solved with indexing. If a query is slow due to heavy aggregation across millions of rows, pre-computing, caching, or serving from a materialized view might be a better solution. Indexes are great for finding rows quickly, but they don't eliminate expensive computations. To audit existing indexes in a codebase, retrieve a list of all indexes and their sizes, cross-reference them with actual query logs, remove unused indexes, check the order of columns in composite indexes against frequent queries, and re-run EXPLAIN on your slowest queries to ensure they're being used.