README

The Laravel Query Optimizer is a package designed to monitor, analyze, and optimize database query performance in Laravel applications. It provides tools to log query execution details, analyze performance metrics, and suggest improvements through a user-friendly dashboard and API endpoints. The package leverages Doctrine DBAL for advanced query analysis and integrates seamlessly with Laravel's service provider architecture.

Demos

Here are some demo images showcasing the Laravel Query Optimizer package:

Features

Query Logging: Records SQL queries, their bindings, execution time, and timestamps.
Performance Dashboard: Visualizes query statistics, execution times, and query distribution.
API Endpoints: Retrieve query metrics and get detailed query explanations using an external AI service (Gemini API).
Console Commands: View and clear query statistics from the command line.
Customizable Configuration: Configure log paths, API keys, and other settings.
Doctrine DBAL Integration: Offers advanced database schema analysis for query optimization.

Requirements

PHP >= 8.0
Laravel >= 8.x
Doctrine DBAL
Supported database drivers: MySQL, PostgreSQL, SQLite, SQL Server

Installation

Install via Composer:

composer require oussemakh1/laravel-query-optimizer

Publish Configuration:
```
php artisan vendor:publish --tag=config
```
This will copy the configuration file to config/queryoptimizer.php.
(Optional) Publish Views:
```
php artisan vendor:publish --tag=queryoptimizer-views
```
This will copy dashboard views to resources/views/vendor/queryoptimizer.
(Optional) Publish Assets:
```
php artisan vendor:publish --tag=assets
```
This will copy JavaScript assets to resources/js/queryoptimizer.

Configure Gemini API Key (for query explanations) in config/queryoptimizer.php:

return [
    'log_path'       => storage_path('logs/query_optimizer.log'),
    'gemini_api_key' => env('GEMINI_API_KEY', ''),
];

Then add GEMINI_API_KEY to your .env file.

Configuration

Edit config/queryoptimizer.php to customize:

log_path: Path for query logs (default: storage/logs/query_optimizer.log).
gemini_api_key: API key for Gemini AI service.
Other settings as needed.

Usage

Dashboard

Visit /query-optimizer/dashboard to see:

Total queries executed
Total & average execution time
Slowest query details
Execution time charts
Query distribution over time
Detailed logs with filters (all, fast, medium, slow)

API Endpoints

Under /api/query-optimizer:

GET /metrics – Returns performance statistics.
POST /explain – Analyzes an SQL query and returns an explanation (requires Gemini API key).

Example:

curl -X POST http://your-app.com/api/query-optimizer/explain \
  -H "Content-Type: application/json" \
  -d '{"sql": "SELECT * FROM users WHERE created_at > NOW()"}'

Console Commands

Show Stats:
```
php artisan query-optimizer:stats
```
Clear Stats:
```
php artisan query-optimizer:clear-stats
```

How It Works

Service Provider: Registers QueryAnalyzer, sets up routes, views, and commands.
Query Logging: QueryLogger class logs details to the configured file.
Analysis: QueryAnalyzer tracks queries, manages stats, and integrates Doctrine DBAL for schema insights.
Dashboard: DashboardController renders a Blade view (dashboard.blade.php) for metrics.
API: ApiController handles metrics retrieval and query explanations via Gemini.
Console: ShowStats & ClearStats commands manage stats from the CLI.

Customization

Views: Modify published Blade templates in resources/views/vendor/queryoptimizer.
JavaScript: Extend assets in resources/js/queryoptimizer.
Config: Adjust config/queryoptimizer.php as needed.

Troubleshooting

Empty Dashboard: Check that the log_path file is writable and receiving logs.
API Errors: Verify the Gemini API key and network connectivity.
DBAL Issues: Ensure correct database configuration and driver.

Contributing

Contributions are welcome! Please submit a pull request or open an issue on GitHub.

License

Open-source under the MIT License.

oussemakh1 / laravel-query-optimizer

Maintainers

Details