/database-indexing-strategy

name: database-indexing-strategy
description: >
  Design and implement database indexing strategies. Use when creating indexes,
  choosing index types, or optimizing index performance in PostgreSQL and MySQL.

Database Indexing Strategy

Table of Contents

• [Overview](#overview)
• [When to Use](#when-to-use)
• [Quick Start](#quick-start)
• [Reference Guides](#reference-guides)
• [Best Practices](#best-practices)

Overview

Design comprehensive indexing strategies to improve query performance, reduce lock contention, and maintain data integrity. Covers index types, design patterns, and maintenance procedures.

When to Use

• Index creation and planning
• Query performance optimization through indexing
• Index type selection (B-tree, Hash, GiST, BRIN)
• Composite and partial index design
• Index maintenance and monitoring
• Storage optimization with indexes
• Full-text search index design

Quick Start

**B-tree Indexes (Default):**

-- Standard equality and range queries
CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_orders_created_at ON orders(created_at DESC);

-- Composite indexes for multi-column queries
CREATE INDEX idx_orders_user_status
ON orders(user_id, status)
WHERE cancelled_at IS NULL;

Reference Guides

Detailed implementations in the `references/` directory:

| Guide | Contents | |---|---| | [PostgreSQL Index Types](references/postgresql-index-types.md) | PostgreSQL Index Types | | [MySQL Index Types](references/mysql-index-types.md) | MySQL Index Types | | [Single Column Indexes](references/single-column-indexes.md) | Single Column Indexes, Composite Indexes, Partial/Filtered Indexes, Expression Indexes |

Best Practices

✅ DO

• Follow established patterns and conventions
• Write clean, maintainable code
• Add appropriate documentation
• Test thoroughly before deploying

❌ DON'T

• Skip testing or validation
• Ignore error handling
• Hard-code configuration values