Database Analyzer

Database Analyzer Skill

This skill helps you analyze database schemas, identify optimization opportunities, and understand table relationships.

Instructions

1. Identify the target: Determine which table or schema to analyze 2. Gather context: Understand the current usage patterns and performance concerns 3. Analyze structure: Examine table definitions, indexes, and relationships 4. Identify issues: Look for missing indexes, improper data types, or inefficient structures 5. Suggest improvements: Provide specific, actionable recommendations

Examples

Example 1: Basic Table Analysis

User request: "Analyze the users table for optimization opportunities"

Approach:

• Check table structure and data types
• Verify indexes on frequently queried columns
• Look for redundant or missing indexes
• Suggest appropriate data types for columns

Analysis Steps:

-- 1. Get table structure
DESCRIBE users;

-- 2. Check existing indexes
SHOW INDEX FROM users;

-- 3. Analyze table statistics
ANALYZE TABLE users;

Common Issues to Check:

• Missing indexes on foreign keys
• Text columns that should be ENUM or SET
• Missing or excessive indexes
• Improper data types (e.g., VARCHAR when INT would suffice)

Example 2: Performance Investigation

User request: "Why are queries on the orders table slow?"

Approach:

• Identify frequently executed queries
• Check for missing indexes on WHERE/JOIN columns
• Analyze table size and growth patterns
• Suggest partitioning if appropriate

Investigation Steps:

-- 1. Check table size
SELECT
    table_name,
    round(((data_length + index_length) / 1024 / 1024), 2) AS 'Size (MB)'
FROM information_schema.TABLES
WHERE table_name = 'orders';

-- 2. Identify slow queries
SHOW PROCESSLIST;

-- 3. Check query execution plan
EXPLAIN SELECT * FROM orders WHERE customer_id = 123;

Optimization Recommendations:

• Add composite indexes for common query patterns
• Consider partitioning by date for large historical tables
• Archive old data to separate tables
• Optimize data types to reduce row size

Example 3: Index Optimization

User request: "Review indexes on the products table"

Approach:

• List all current indexes
• Identify unused or redundant indexes
• Check for missing indexes on query patterns
• Calculate index selectivity

Review Process:

-- 1. Show all indexes
SHOW INDEX FROM products;

-- 2. Check index usage (MySQL 5.6+)
SELECT * FROM sys.schema_unused_indexes
WHERE object_schema = 'your_database'
  AND object_name = 'products';

-- 3. Analyze query patterns
SELECT DISTINCT column_name
FROM information_schema.statistics
WHERE table_name = 'products';

Requirements

• Access to database schema information
• Understanding of SQL and database design principles
• Ability to read EXPLAIN query plans (if available)
• Knowledge of the application's query patterns

Best Practices

• Always explain the reasoning behind suggestions
• Consider both read and write performance impacts
• Account for data volume and growth patterns
• Suggest incremental improvements when possible
• Document assumptions made during analysis
• Provide migration scripts for proposed changes
• Test recommendations in a non-production environment first

Common Patterns

Pattern 1: E-commerce Database

• Heavy read operations on product catalog
• Frequent JOIN operations between products, categories, and prices
• Date-based queries for orders
• Key optimizations: Composite indexes, query caching, read replicas

Pattern 2: User Management System

• Frequent lookups by email or username
• Session management with expiration
• Role-based access control queries
• Key optimizations: Unique indexes, covering indexes, denormalization

Pattern 3: Analytics Database

• Large aggregation queries
• Time-series data
• Reporting queries with multiple JOINs
• Key optimizations: Partitioning, summary tables, columnstore indexes

Troubleshooting

No Slow Queries Detected

• Check slow query log settings
• Verify logging is enabled
• Look for queries with high execution count (not just slow time)

Index Not Being Used

• Check index selectivity (should be high)
• Verify query uses indexed columns in WHERE clause
• Consider forcing index with USE INDEX hint for testing
• Check for implicit type conversions preventing index use

Table Lock Contention

• Identify long-running transactions
• Consider using InnoDB over MyISAM for row-level locking
• Optimize batch operations to reduce lock time

Resources

Bundled resources in this skill package:

• references/schema-patterns.sql - Common schema patterns
• scripts/analyze-table.php - Automated analysis script
• assets/optimization-checklist.md - Comprehensive checklist

Use base directory from composer read-skill output to locate these files.

Notes

• Always backup before making schema changes
• Test in development environment first
• Monitor performance before and after changes
• Document all modifications for team awareness

Database Analyzer Skill - Example Package

This is a reference implementation of an AI agent skill package for the Composer AI Agent Skill Plugin.

Package Structure

database-analyzer-skill/
├── composer.json         # Package metadata with type: ai-agent-skill
├── SKILL.md             # Skill definition following Claude Code schema
├── README.md            # This file
├── src/                 # Optional: PHP helper classes
├── references/          # Optional: Reference files (SQL patterns, examples)
├── scripts/             # Optional: Utility scripts
└── assets/              # Optional: Additional resources

Key Components

composer.json

The most important part is setting the package type:

{
  "name": "example/database-analyzer-skill",
  "type": "ai-agent-skill",    ← This makes it discoverable
  "description": "AI agent skill for database schema analysis"
}

SKILL.md

Must follow the Claude Code SKILL.md specification:

Required Frontmatter:

---
name: database-analyzer        # lowercase, numbers, hyphens (max 64 chars)
description: What it does and when to use it (max 1024 chars)
---

Content Structure:

• Instructions for using the skill
• Examples with real-world scenarios
• Requirements and prerequisites
• Best practices and common patterns

How It Works

1. Installation: User installs this package via Composer 2. Discovery: Plugin detects type: ai-agent-skill during install/update 3. Registration: Plugin parses SKILL.md and registers in AGENTS.md 4. Usage: AI agents discover skill via AGENTS.md XML index 5. Invocation: Agents execute composer read-skill database-analyzer 6. Execution: Full SKILL.md content loaded with base directory path

Publishing Your Skill

1. Create Repository

mkdir my-skill
cd my-skill
git init

2. Add Files

# Copy this example as a template
cp -r examples/database-analyzer-skill/* .

# Customize for your skill
vim composer.json  # Update name, description, authors
vim SKILL.md       # Write your skill instructions

3. Test Locally

# Create a test project
mkdir ../test-project
cd ../test-project
composer init

# Add your skill as a local path repository
composer config repositories.my-skill path ../my-skill
composer require vendor/my-skill

# Verify registration
composer list-skills
composer read-skill my-skill

4. Publish to Packagist

# Tag a release
git tag 1.0.0
git push origin main --tags

# Submit to https://packagist.org

Configuration Options

Default (Convention)

No configuration needed if SKILL.md is in package root:

my-skill/
├── composer.json
├── SKILL.md          ← Auto-discovered
└── src/

Custom Path

For skill in non-root location:

{
  "type": "ai-agent-skill",
  "extra": {
    "ai-agent-skill": "docs/my-skill.md"
  }
}

Multiple Skills

For packages with multiple skills:

{
  "type": "ai-agent-skill",
  "extra": {
    "ai-agent-skill": [
      "skills/analyzer.md",
      "skills/optimizer.md",
      "skills/validator.md"
    ]
  }
}

SKILL.md Requirements

Name Field

• Format: ^[a-z0-9-]{1,64}$
• Only lowercase letters, numbers, and hyphens
• Maximum 64 characters
• Examples: database-analyzer, symfony-security, api-validator

Description Field

• Maximum 1024 characters
• Should explain WHAT the skill does AND WHEN to use it
• Good: "Analyze database schemas and suggest optimizations. Use when working with table structure or query performance."
• Bad: "Database helper" (too vague)

Optional: allowed-tools

For Claude Code integration:

---
name: my-skill
description: What it does
allowed-tools: [Read, Grep, Glob, Bash]  # Restrict tools
---

Bundled Resources

Skills can include additional files:

my-skill/
├── SKILL.md
├── references/
│   ├── sql-patterns.sql
│   └── examples.md
├── scripts/
│   ├── analyze.php
│   └── validate.sh
└── assets/
    └── checklist.md

Reference in SKILL.md:

## Resources

See bundled files:
- `references/sql-patterns.sql` - Common SQL patterns
- `scripts/analyze.php` - Analysis automation script

Use the base directory from `composer read-skill` output to locate files.

Agent Usage:

$ composer read-skill my-skill
Base Directory: vendor/vendor/my-skill
# Agent can now access: vendor/vendor/my-skill/references/sql-patterns.sql

Validation

Test your SKILL.md:

# Install the plugin in a test project
composer require netresearch/composer-agent-skill-plugin

# Install your skill
composer require vendor/my-skill

# Check if it appears
composer list-skills

# Read full content
composer read-skill my-skill

Common Issues:

1. Skill not found: Check type: ai-agent-skill in composer.json 2. Invalid frontmatter: Validate YAML syntax and required fields 3. Wrong name format: Use only lowercase, numbers, and hyphens 4. Description too long: Keep under 1024 characters

Tips for Great Skills

✅ Do:

• Provide clear, step-by-step instructions
• Include real-world examples with code
• Document prerequisites and requirements
• Explain reasoning behind recommendations
• Keep language clear and concise
• Test thoroughly before publishing

❌ Don't:

• Use vague descriptions
• Include outdated information
• Assume knowledge without documenting
• Make skills too broad or too narrow
• Forget to version your releases

Support

For questions about creating skills:

• Plugin Documentation
• Claude Code SKILL.md Spec
• openskills Project

License

MIT License - feel free to use this as a template for your own skills.