Overview

The Database Analyzer inspects your schema to identify optimization opportunities and improve relationships and indexes. It helps boost query performance, data integrity, and maintainability by recommending concrete changes to tables, keys, and indexing strategies. It assumes PHP 8.1+ and a live database connection via PDO.

How This Skill Works

It connects to your database using PDO (PHP 8.1+ required) and reads schema metadata (tables, columns, primary/foreign keys, and existing indexes). It analyzes relationship quality, index coverage, and query patterns to surface concrete recommendations, including new or adjusted indexes and relationship refinements. It can output a prioritized action list for developers to implement.

When to Use It

• When legacy schemas have slow read queries due to missing or misaligned indexes
• Before adding new features that affect relationships or foreign keys
• During migration or refactor to optimize join performance and data integrity
• When evaluating complex foreign key relationships and cascade rules
• When preparing a performance review or audit of database schemas

Quick Start

1. Step 1: Provide database connection details (host, database, user, password) and ensure PDO access from PHP 8.1+
2. Step 2: Run a schema analysis to generate a prioritized recommendations report
3. Step 3: Review the recommendations and implement changes in a staging environment before production

Best Practices

• Start with a full schema map: tables, keys, indexes, and relationships
• Prioritize index changes based on actual query patterns and explained plans
• Validate candidate foreign keys and relationships against business logic
• Test recommendations in a staging environment before applying to production
• Document changes and monitor performance after deployment

Example Use Cases

• Identify missing indexes on high-traffic read paths like users.login or orders.created_at
• Recommend composite indexes for multi-column filters (e.g., users.email + status)
• Flag redundant or unused indexes causing write amplification
• Suggest foreign key refinements to remove circular dependencies
• Propose denormalization or partitioning strategies for large tables with joins

Frequently Asked Questions