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

Issue	Severity	Description
`SELECT *` usage	Medium	Recommends selecting specific columns
N+1 queries	High	Detects repeated similar queries
Leading wildcard LIKE	Medium	`LIKE '%term'` prevents index usage
Subqueries in WHERE	Medium	Suggests JOIN alternatives
ORDER BY without LIMIT	Low	Warns about potential memory issues

SQL Server Specific

Issue	Severity	Description
Implicit conversions	High	Type mismatches that prevent index usage
Missing NOLOCK hints	Low	Suggests for read-heavy queries

Safety Checks

Issue	Severity	Description
UPDATE without WHERE	Critical	Warns about full-table updates
DELETE without WHERE	Critical	Warns about full-table deletes

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:

Indexes aren't magic - They help reads but hurt writes. Profile your actual workload.
N+1 is the silent killer - A page loading 100 products with $product->category->name makes 101 queries. Use eager loading.
COUNT(*) on large tables is expensive - Consider caching counts or using estimates for UI.
SQL Server parameter sniffing - First execution plan gets cached. Use OPTION (RECOMPILE) for highly variable queries.
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

mystus / laravel-query-insights

Maintainers

Details