README

A Laravel package for monitoring, analysing, and optimising database query performance. Built from 20+ years of experience optimising SQL Server and MySQL databases in production environments.

Features

• 🔍 Automatic Query Monitoring - Captures all database queries with execution times
• ⚡ Slow Query Detection - Configurable threshold for identifying performance bottlenecks
• 🔄 N+1 Query Detection - Automatically identifies potential N+1 query patterns
• 💡 Actionable Suggestions - Get specific recommendations for query optimisation
• 🗄️ SQL Server Support - Includes SQL Server-specific optimisation hints
• 📊 Artisan Reporting - Generate reports from stored query data
• 🎯 Zero Configuration - Works out of the box with sensible defaults

Installation

composer require mystus/laravel-query-insights

The package will auto-register its service provider.

Publish Configuration (Optional)

php artisan vendor:publish --tag=query-insights-config

Quick Start

Query Insights starts monitoring automatically. Access the data anywhere in your application:

use Mystus\QueryInsights\Facades\QueryInsights;

// Get all recorded queries
$queries = QueryInsights::getQueries();

// Get only slow queries
$slowQueries = QueryInsights::getSlowQueries();

// Get queries with optimisation suggestions
$needsWork = QueryInsights::getQueriesWithSuggestions();

// Get a summary
$summary = QueryInsights::getSummary();

Configuration

// config/query-insights.php

return [
    // Enable/disable monitoring
    'enabled' => env('QUERY_INSIGHTS_ENABLED', true),

    // Queries slower than this (ms) are flagged
    'slow_threshold' => env('QUERY_INSIGHTS_SLOW_THRESHOLD', 100),

    // Log slow queries to your log file
    'log_slow_queries' => env('QUERY_INSIGHTS_LOG_SLOW', true),

    // Store queries for later analysis
    'store_queries' => env('QUERY_INSIGHTS_STORE', false),

    // Days to retain stored data
    'retention_days' => env('QUERY_INSIGHTS_RETENTION_DAYS', 7),

    // Similar queries before N+1 warning
    'n_plus_one_threshold' => env('QUERY_INSIGHTS_N_PLUS_ONE_THRESHOLD', 5),
];

What It Detects

Performance Issues

SQL Server Specific

Safety Checks

Usage Examples

Display in Development

Add to your AppServiceProvider or create a custom middleware:

use Mystus\QueryInsights\Facades\QueryInsights;

// In a view composer or at end of request
$summary = QueryInsights::getSummary();

// $summary contains:
// [
//     'total_queries' => 45,
//     'total_time' => 234.56,
//     'average_time' => 5.21,
//     'slow_queries' => 2,
//     'queries_with_suggestions' => 5,
//     'slowest_query' => [...],
// ]

Temporary Monitoring

use Mystus\QueryInsights\Facades\QueryInsights;

// Disable during bulk operations
QueryInsights::disable();

// ... bulk insert operations ...

QueryInsights::enable();

Adjust Threshold Dynamically

// Be more strict for critical paths
QueryInsights::setSlowThreshold(50);

// Process order...

// Reset for less critical operations
QueryInsights::setSlowThreshold(200);

Generate Reports

Enable storage in your config or .env:

QUERY_INSIGHTS_STORE=true

Then run reports:

# Today's report
php artisan query-insights:report

# Specific date
php artisan query-insights:report --date=2025-01-02

# Only slow queries
php artisan query-insights:report --slow-only

# Only queries with suggestions
php artisan query-insights:report --with-suggestions

Testing

composer test

Real-World Performance Tips

This package was built from experience optimising databases serving 170+ hospitals with millions of records. Here are some hard-won lessons:

1. Indexes aren't magic - They help reads but hurt writes. Profile your actual workload.

2. N+1 is the silent killer - A page loading 100 products with $product->category->name makes 101 queries. Use eager loading.

3. COUNT(*) on large tables is expensive - Consider caching counts or using estimates for UI.

4. SQL Server parameter sniffing - First execution plan gets cached. Use OPTION (RECOMPILE) for highly variable queries.

5. Don't trust ORMs blindly - Always check ->toSql() or enable query logging during development.

Contributing

Contributions are welcome! Please see CONTRIBUTING.md for details.

License

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

Credits

• Matthew Fritz
• Built with lessons learned from healthcare technology systems in South Africa