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