README

A comprehensive query performance monitoring and optimization package for Laravel. Catch N+1 queries, identify slow queries, get index recommendations, and track performance over time.

Features

N+1 Query Detection - Automatically detect and report N+1 query patterns
Slow Query Detection - Identify queries exceeding configurable thresholds with optimization suggestions
Index Advisor - Get intelligent index recommendations based on query patterns
Query Explanation - Analyze EXPLAIN plans with efficiency scoring
Performance Baselines - Create and compare performance baselines across deployments
Query Budgets - Enforce per-endpoint query limits with configurable actions
Trend Analysis - Track performance trends over time with visual sparklines
Real-time Alerts - Get notified about performance issues via Slack, email, or console
Dashboard - Vue 3 SPA dashboard for visualizing query metrics

Requirements

PHP 8.3+
Laravel 11.0+ or 12.0+

Installation

Install the package via composer:

composer require marwen-brini/smart-query-analyzer --dev

Publish and run the migrations:

php artisan vendor:publish --tag="smart-query-analyzer-migrations"
php artisan migrate

Publish the config file:

php artisan vendor:publish --tag="smart-query-analyzer-config"

Usage

Basic Setup

The package automatically starts monitoring queries when enabled. Add the middleware to your routes:

// In app/Http/Kernel.php or bootstrap/app.php
->withMiddleware(function (Middleware $middleware) {
    $middleware->append(\SmartQueryAnalyzer\Http\Middleware\QueryAnalyzerMiddleware::class);
})

Using the Facade

use SmartQueryAnalyzer\Facades\SmartQueryAnalyzer;

// Start listening to queries
SmartQueryAnalyzer::startListening();

// Pause/resume logging
SmartQueryAnalyzer::pause();
SmartQueryAnalyzer::resume();

// Get captured queries and stats
$queries = SmartQueryAnalyzer::getQueries();
$stats = SmartQueryAnalyzer::getStats();

// Flush queries to database
SmartQueryAnalyzer::flush();

Artisan Commands

Analyze Queries

# Analyze queries for a specific request
php artisan query:analyze --request-id=abc123

# Analyze queries for an endpoint
php artisan query:analyze --endpoint=/api/users --method=GET

# Analyze last N requests
php artisan query:analyze --last=10

Slow Queries

# List slow queries
php artisan query:slow

# Custom threshold (ms)
php artisan query:slow --threshold=50

# Filter by endpoint
php artisan query:slow --endpoint=/api/users

# Time window
php artisan query:slow --since="1 hour ago"

Query Explanation

# Explain a query interactively
php artisan query:explain "SELECT * FROM users WHERE email = 'test@example.com'"

# Output formats: table, json, raw
php artisan query:explain "SELECT * FROM users" --format=json

# From file
php artisan query:explain --file=query.sql

Index Recommendations

# Generate index recommendations
php artisan query:indexes

# Detect unused indexes
php artisan query:indexes --unused

# Generate migration file
php artisan query:indexes --migrate

# List pending recommendations
php artisan query:indexes --pending

Performance Baselines

# Create baseline for all endpoints
php artisan query:baseline --create

# Create baseline for specific endpoint
php artisan query:baseline --create --endpoint=/api/users

# List all baselines
php artisan query:baseline --list

# Export baselines to JSON
php artisan query:baseline --export=baselines.json

# Import baselines from JSON
php artisan query:baseline --import=baselines.json

Regression Testing

# Test against baseline
php artisan query:test

# Custom regression threshold (percentage)
php artisan query:test --threshold=20

# Test specific endpoint
php artisan query:test --endpoint=/api/users

# CI mode with minimal output
php artisan query:test --ci

# JSON output
php artisan query:test --json

Compare Performance

# Compare current vs baseline
php artisan query:compare

# Filter by commit/branch
php artisan query:compare --commit=abc123
php artisan query:compare --branch=feature/new

Query Budgets

# Show budget status for all endpoints
php artisan query:budgets

# Show violations only
php artisan query:budgets --violations

# JSON output
php artisan query:budgets --json

Trend Analysis

# Show performance trends
php artisan query:trends

# Custom time period
php artisan query:trends --period=7d

# Interval: hour, day, week
php artisan query:trends --interval=hour

# JSON output
php artisan query:trends --json

Dashboard Management

# Show dashboard status
php artisan query:dashboard

# Enable/disable dashboard
php artisan query:dashboard --enable
php artisan query:dashboard --disable

# Cleanup old logs
php artisan query:dashboard --cleanup

Configuration

Key configuration options in config/smart-query-analyzer.php:

return [
    // Enable/disable the analyzer
    'enabled' => env('SMART_QUERY_ANALYZER_ENABLED', true),

    // Active environments
    'environments' => ['local', 'development', 'staging'],

    // N+1 detection settings
    'n_plus_one' => [
        'enabled' => true,
        'threshold' => 5, // Occurrences before flagging
        'ignore_patterns' => [],
    ],

    // Slow query settings
    'slow_queries' => [
        'threshold' => 100, // milliseconds
        'explain_enabled' => true,
        'capture_bindings' => true,
        'capture_backtrace' => true,
    ],

    // Query budgets per endpoint
    'budgets' => [
        'enabled' => true,
        'violation_action' => 'log', // log, warn, exception
        'endpoints' => [
            '/api/users' => ['max_queries' => 10, 'max_time' => 500],
            '/api/*' => ['max_queries' => 20, 'max_time' => 1000],
        ],
        'default' => ['max_queries' => 50, 'max_time' => 2000],
    ],

    // Alert channels
    'alerts' => [
        'slow_query' => ['channels' => ['log', 'slack']],
        'n_plus_one' => ['channels' => ['log']],
    ],

    // Dashboard settings
    'dashboard' => [
        'enabled' => true,
        'path' => 'query-analyzer',
        'middleware' => ['web', 'auth'],
    ],

    // Data retention
    'storage' => [
        'retention_days' => 30,
    ],
];

Database Models

The package creates the following tables:

query_analyzer_logs - Stores all captured queries
query_analyzer_n_plus_one - N+1 detection records
query_analyzer_baselines - Performance baselines
query_analyzer_index_recommendations - Index suggestions

Multi-Database Support

The package supports:

MySQL / MariaDB
PostgreSQL
SQLite

Testing

composer test

Changelog

Please see CHANGELOG for more information on what has changed recently.

Contributing

Please see CONTRIBUTING for details.

Security Vulnerabilities

Please review our security policy on how to report security vulnerabilities.

Credits

License

The MIT License (MIT). Please see License File for more information.

marwen-brini / smart-query-analyzer

Maintainers

Details