Query Performance Analysis & Index Management

Context (Input)

Use this skill when:

• New or modified endpoints are slow
• Profiler shows many database queries for single operation
• Need to detect N+1 query problems
• Query execution time is high
• Slow query warnings in MySQL logs
• Performance regression after code changes
• Planning safe index migrations for production
• Need to verify index effectiveness

Task (Function)

Analyze query performance, detect N+1 issues, identify missing indexes, and create safe online index migrations with verification steps.

Success Criteria:

• N+1 queries detected and fixed
• Slow queries identified with EXPLAIN analysis
• Missing indexes detected and added
• Query performance meets acceptable thresholds (<100ms for reads, <500ms for writes)
• Index migrations are safe for production (minimal downtime)
• Performance regression tests added

TL;DR - Quick Performance Checklist

Before Merging Code:

• Run endpoint with profiler - check query count
• No N+1 queries (queries in loops)
• Slow queries (<100ms) analyzed with EXPLAIN
• Missing indexes identified and added
• Eager loading used where appropriate
• Query count reasonable for operation (<10 queries ideal)
• Performance test added to prevent regression

When Adding Indexes:

• Index covers actual query patterns
• Composite index field order correct
• Index creation uses ALGORITHM=INPLACE when possible
• Verification steps included
• Index usage confirmed with EXPLAIN

Quick Start: 5-Step Performance Analysis

Step 1: Enable MySQL Slow Query Log

docker compose exec database mariadb -u root -proot

-- Enable slow query log
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 0.1;  -- Log queries slower than 100ms
SET GLOBAL log_queries_not_using_indexes = 'ON';

-- Verify settings
SHOW VARIABLES LIKE 'slow_query%';
SHOW VARIABLES LIKE 'long_query_time';

Step 2: Run Your Endpoint

curl https://localhost/api/users

Step 3: Analyze Query Patterns

-- View recent slow queries (MariaDB)
SELECT * FROM mysql.slow_log ORDER BY start_time DESC LIMIT 10;

-- Or use Symfony Profiler for detailed query analysis
-- Open: https://localhost/_profiler

Step 4: Check for Performance Issues

N+1 Problem Symptoms:

• Same query executed many times
• Query count grows with data size
• Queries inside foreach loops

Slow Query Symptoms:

• Execution time >100ms
• EXPLAIN shows type: ALL (full table scan)
• High rows vs actual returned rows

Step 5: Disable Slow Query Log (Production)

SET GLOBAL slow_query_log = 'OFF';

Common Performance Issues

Issue 1: N+1 Queries

Detection: 100+ queries for 100 records

Fix: Use eager loading with Doctrine

// ❌ BAD: N+1 problem
$users = $repository->findAll();  // 1 query
foreach ($users as $user) {
    $token = $user->getConfirmationToken();  // N queries if lazy loaded!
}

// ✅ GOOD: Eager loading with QueryBuilder
$qb = $this->createQueryBuilder('u');
$qb->leftJoin('u.confirmationToken', 't')
   ->addSelect('t');  // Eager load tokens
$users = $qb->getQuery()->getResult();

See: examples/n-plus-one-detection.md for complete guide

Issue 2: Slow Queries (No Index)

Detection: EXPLAIN shows type: ALL, execution time >100ms

Fix: Add index

-- Check query performance
EXPLAIN SELECT * FROM users WHERE email = 'test@example.com';

-- If type: ALL → add index

Add index in Doctrine migration:

// migrations/VersionXXX.php
public function up(Schema $schema): void
{
    $this->addSql('CREATE INDEX idx_users_email ON users (email)');
}

Or in XML mapping:

<!-- config/doctrine/User.orm.xml -->
<indexes>
    <index name="idx_email" columns="email"/>
</indexes>

See: examples/slow-query-analysis.md for EXPLAIN interpretation

Issue 3: Missing Indexes on Filtered Fields

Detection: Queries filter/sort on fields without indexes

Common patterns needing indexes:

• WHERE clause fields: email = ?, status = ?
• ORDER BY fields: created_at DESC
• Composite filters: status = ? AND type = ?
• Foreign keys: user_id, token_id

Cursor pagination + UUID index strategy (this repo):

This service uses cursor pagination on id (UUID). For pagination with filters, use a composite index:

<indexes>
    <index name="idx_status_id" columns="status,id"/>
</indexes>

See: reference/index-strategies.md for index selection guide

Performance Thresholds

See: reference/performance-thresholds.md for complete thresholds

Safe Index Migrations

MariaDB 11.4+ supports online DDL:

• Most index operations are non-blocking with ALGORITHM=INPLACE
• InnoDB allows concurrent reads and writes during index builds
• Note: Large tables may still cause brief locks at start/end

Recommendation: For production index builds, schedule during low-traffic periods for very large tables.

Production Migration Strategy

1. Create Doctrine migration with index
2. Use ALGORITHM=INPLACE for non-blocking creation
3. Schedule during low traffic for large tables
4. Run migration: make doctrine-migrations-migrate
5. Verify index created: SHOW INDEX FROM table_name
6. Verify index is used: Run EXPLAIN on queries
7. Measure performance improvement

// Migration example with online DDL
public function up(Schema $schema): void
{
    $this->addSql('CREATE INDEX idx_users_email ON users (email) ALGORITHM=INPLACE LOCK=NONE');
}

Performance Testing

final class UserEndpointPerformanceTest extends ApiTestCase
{
    public function testNoN1Queries(): void
    {
        // Arrange: Create test data
        for ($i = 0; $i < 50; $i++) {
            $this->createUser();
        }

        // Act: Enable query counter
        $this->enableQueryCounter();
        $this->client->request('GET', '/api/users');

        // Assert: Should have minimal queries
        $queryCount = $this->getQueryCount();
        $this->assertLessThan(10, $queryCount, 'N+1 query detected!');
    }

    public function testEndpointPerformance(): void
    {
        // Measure response time
        $start = microtime(true);
        $response = $this->client->request('GET', '/api/users');
        $duration = (microtime(true) - $start) * 1000;

        // Assert: Should be fast
        $this->assertLessThan(200, $duration, "Too slow: {$duration}ms");
    }
}

Quick Commands Reference

# Connect to MySQL
docker compose exec database mariadb -u root -proot db

-- Enable slow query log
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 0.1;

-- View slow queries
SELECT * FROM mysql.slow_log ORDER BY start_time DESC LIMIT 10;

-- Check indexes
SHOW INDEX FROM users;

-- EXPLAIN query
EXPLAIN SELECT * FROM users WHERE email = 'test@example.com';

-- EXPLAIN with extended info
EXPLAIN ANALYZE SELECT * FROM users WHERE email = 'test@example.com';

-- IMPORTANT: Disable slow query log in production
SET GLOBAL slow_query_log = 'OFF';

# Create migration
make doctrine-migrations-generate

# Run migration
make doctrine-migrations-migrate

# Validate schema
docker compose exec php bin/console doctrine:schema:validate

Workflow Integration

When to Use This Skill

Use after:

• api-platform-crud - After creating endpoints
• database-migrations - After adding entities

Use before:

• load-testing - Optimize before load testing
• ci-workflow - Validate performance in CI

Related skills:

• testing-workflow - Add performance tests
• documentation-sync - Document performance changes

Reference Documentation

Examples (Detailed Scenarios)

• examples/README.md - Examples index
• examples/n-plus-one-detection.md - Complete N+1 detection and fix guide
• examples/slow-query-analysis.md - EXPLAIN analysis walkthrough

Reference Guides

• reference/performance-thresholds.md - Acceptable performance limits
• reference/mysql-slow-query-guide.md - Complete slow query log documentation
• reference/index-strategies.md - When to use which index type

Comparison: This Skill vs database-migrations

Workflow: Use this skill to identify needed indexes, then use database-migrations for migration syntax.

Troubleshooting

Issue: Can't enable slow query log

Solution: Verify MySQL permissions, ensure connected to correct database

Issue: EXPLAIN shows ALL but index exists

Solution:

1. Verify index covers your query pattern
2. Check composite index field order
3. Ensure query uses indexed fields exactly
4. Check if optimizer chooses full scan for small tables

Issue: Container name error

Solution: Use database as the service name:

docker compose exec database mariadb -u root -proot  # ✅ Correct
docker compose exec mysql mariadb -u root -proot     # ❌ Wrong

Issue: Symfony Profiler not showing queries

Solution: Enable profiler in dev mode:

# config/packages/dev/web_profiler.yaml
web_profiler:
  toolbar: true
  intercept_redirects: false

External Resources

• MySQL EXPLAIN Documentation
• MariaDB Query Optimization
• Doctrine Performance Tips
• Symfony Profiler

Best Practices

DO ✅

• Use Symfony Profiler in development for every new feature
• Analyze queries before deploying to production
• Add performance tests to prevent regressions
• Use eager loading to prevent N+1 queries
• Create indexes for frequently filtered/sorted fields
• Use EXPLAIN ANALYZE for detailed query plans

DON'T ❌

• Leave slow query log enabled in production at low threshold
• Add indexes without analyzing query patterns
• Ignore N+1 warnings (they compound quickly)
• Skip EXPLAIN analysis before adding indexes
• Forget to verify index is actually used after creation
• Add indexes on every column (write overhead)