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:** ```sql -- 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:** ```sql -- 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:** ```sql -- 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