--- title: "Query Indexing Mistakes That Silently Kill Performance" description: "Avoid common database indexing mistakes. Learn how indexes can hurt performance and when NOT to use them for better query speed." slug: "query-indexing-mistakes-that-silently-kill-performance" url: "https://furkanbaytekin.dev/blogs/query-indexing-mistakes-that-silently-kill-performance" author: "Furkan Baytekin" category: "Software" tags: [] published: 2025-12-14T08:39:55Z updated: 2025-12-14T08:39:55Z readTime: 3 cover: "https://myblogcms.furkanbaytekin.dev/uploads/cover/query-indexing-mistakes-that-silently-kill-performance+1765701263284" --- Indexes are supposed to make queries fast. But the wrong indexing strategy can do the opposite - slowing everything down while looking totally innocent. Here are the silent killers you should watch for. --- ## 1. Indexing “Everything Just in Case” New developers love creating indexes for every column. Feels safe, right? Not really. Each index adds write overhead. Inserts, updates, deletes - all get slower because the database has to maintain extra structures. **Rule:** Only index columns that are actively used for lookups, joins, filtering, or sorting. --- ## 2. Missing Composite Indexes A single-column index might look fine, but if your query filters by two or three fields, the database can’t magically combine them. ```sql SELECT * FROM orders WHERE customer_id = 42 AND status = 'paid'; ``` If you only indexed `customer_id`, you still get a slow scan. **Fix:** Add a composite index (`customer_id`, `status`) matching your query pattern. --- ## 3. Wrong Column Order in Composite Indexes Composite indexes are left-to-right. If the leading column isn’t filtered in your query, that index becomes useless. Example index: ```sql INDEX (created_at, status) ``` Query: ```sql SELECT * FROM orders WHERE status = 'shipped'; ``` Here, `status` alone can’t use this index efficiently. **Fix:** Put the most selective (or most frequently filtered) column first. --- ## 4. Forgetting to Index Foreign Keys Foreign keys get hit constantly during joins and cascading operations. Without indexes, you get nasty full-table scans in joins. Example: ```sql SELECT * FROM comments WHERE post_id = 10; ``` If `post_id` isn’t indexed, you’ll feel the pain. --- ## 5. Indexing Low-Cardinality Columns Columns like `gender`, `is_active`, or boolean flags look important. But indexing them rarely helps - values repeat too much. An index where 90% of rows have the same value won’t filter anything efficiently. **Rule:** Skip indexing columns with very low cardinality unless they’re part of a composite index. --- ## 6. Ignoring Covering Indexes Sometimes the index contains all the columns your query needs. That means the database never touches the table - huge win. If you do this: ```sql SELECT id, email FROM users WHERE email = 'a@b.com'; ``` An index on `(email, id)` becomes a covering index. Many teams miss this small trick. --- ## 7. Not Watching Query Plans Developers often “think” their index is used. The database disagrees. Run: ```sql EXPLAIN ANALYZE ... ``` If it says **Seq Scan**, you’re leaving performance on the table. --- ## 8. Overlooking Index Bloat Dead tuples, outdated stats, oversized indexes - all slow down reads. PostgreSQL especially can accumulate bloat. **Fix:** - Run `VACUUM` / `ANALYZE` regularly - Drop unused indexes - Rebuild heavily bloated ones --- ## 9. Forgetting Partial Indexes If you have a table where only a subset matters, partial indexes are gold. Example: ```sql CREATE INDEX idx_active_users ON users (email) WHERE deleted_at IS NULL; ``` Faster, smaller, smarter. --- ## 10. Using Functions Without Functional Indexes If you filter with a function, your normal index becomes useless. ```sql WHERE LOWER(email) = 'test@mail.com' ``` Solution: ```sql CREATE INDEX idx_lower_email ON users (LOWER(email)); ``` --- ## Final Thoughts Query performance issues often hide in plain sight. A bad index is worse than no index - silent, expensive, and hard to spot. Audit your indexes regularly. Check your slow query logs. Read your query plans. Your database (and your users) will thank you. --- Album of the blog: