After years of Rails development, here are the indexing mistakes I see (and have made) most often:
1. **Missing foreign key indexes** — Rails doesn't add these automatically. Always index your `_id` columns.
2. **Over-indexing** — Every index slows down writes. Don't index columns you never query.
3. **Wrong column order in composite indexes** — Put the most selective column first.
4. **Ignoring partial indexes** — If you only query active records, index with a WHERE clause.
5. **Not using EXPLAIN** — It's the single best tool for understanding query performance.
What would you add to this list?
Comments (3)
Log in to reply to this post.
Number 1 is so common! I always add `index: true` to my `references` columns in migrations now. Rails should honestly do this by default.
Actually, Rails does add indexes on foreign keys by default now when you use `references` in a migration! But you're right that older apps often have missing ones.
I'd add: not using `EXPLAIN ANALYZE` (as opposed to just `EXPLAIN`). The estimated vs actual row counts can be very different.