Laravel MySQL Advisor

What it solves

MySQL already records which indexes are never read, which statements scan whole tables and which tiny query runs thousands of times, but nothing in a Laravel app reads that for you.

db:show lists sizes and db:monitor counts connections; neither tells you which index to drop or which relation to eager-load.

The advisor reads information_schema, performance_schema and server status, and reports each problem with its severity, the numbers behind it and, where one applies, the fix as SQL and as a Laravel migration or Eloquent snippet. It never runs a fix: every statement it sends is a read.

• Index checks: unused, duplicate and left-prefix indexes, and *_id columns or foreign keys with no index
• Query checks from performance_schema: full table scans, top statements by total time, and N+1 suspects
• Table and server checks: missing primary keys, large and fragmented tables, stale statistics, buffer pool, disk temp tables and connection headroom
• Each fix shown as SQL and as a Laravel migration; unused indexes are hidden first (INVISIBLE on MySQL, IGNORED on MariaDB)
• Artisan report with JSON output and a --fail-on exit code for CI
• Blade dashboard that opens only in the local environment until you define the viewMysqlAdvisor gate

Install

Requires PHP 8.3+ and Laravel 12 and 13.

composer require amjadiqbal/laravel-mysql-advisor

Usage

// app/Providers/AppServiceProvider.php: who may open /mysql-advisor outside local
use Illuminate\Support\Facades\Gate;

Gate::define('viewMysqlAdvisor', function ($user = null) {
    return $user?->email === '[email protected]';
});

// Then run: php artisan mysql-advisor:report

Setup

Needs MySQL 8.0+ or MariaDB 10.6+. The service provider registers itself. Publish the config to change thresholds or the list of checks. The index-usage and query checks read performance_schema, which is on by default in MySQL and off by default in MariaDB; when it is off or not readable, those checks are listed as skipped with the GRANT or setting that enables them, and the other checks still run.

1. 1 php artisan vendor:publish --tag=mysql-advisor-config
2. 2 Optional: point MYSQL_ADVISOR_CONNECTION at a connection whose user can only SELECT.

CREATE USER 'advisor'@'%' IDENTIFIED BY 'a-long-password';
GRANT SELECT ON your_app.* TO 'advisor'@'%';
GRANT SELECT ON performance_schema.* TO 'advisor'@'%';
GRANT SELECT ON mysql.innodb_table_stats TO 'advisor'@'%';

Make your queries visible

Laravel sends native prepared statements by default, and MySQL and MariaDB keep no per-query statistics for those. With Laravel's defaults the three query checks are reported as skipped, with the reason. To make every query visible, switch the connection to emulated prepares; try it on staging first.

// config/database.php, in your mysql connection
'options' => extension_loaded('pdo_mysql') ? array_filter([
    PDO::MYSQL_ATTR_SSL_CA => env('MYSQL_ATTR_SSL_CA'),
    PDO::ATTR_EMULATE_PREPARES => true,
]) : [],

The artisan report and CI

Exit code 0 means no finding at or above --fail-on (default critical), 1 means at least one, and 2 means the database could not be inspected. Run the schema checks in CI against the database your tests migrate; run the usage-based checks on a schedule against staging or a replica.

php artisan mysql-advisor:report --only=indexes
php artisan mysql-advisor:report --format=json
php artisan mysql-advisor:report --connection=replica
php artisan mysql-advisor:report --only=redundant-index,unindexed-foreign-key,missing-primary-key --fail-on=warning

The dashboard

Open /mysql-advisor for the findings worst first, with a copy button for each fix, category filters, and the skipped checks with the way to enable each one. It is one Blade view with inline CSS, so there is nothing to build or publish. It works in the local environment and returns 403 everywhere else until you define the viewMysqlAdvisor gate. The gate runs after the configured middleware, so changing dashboard.middleware cannot remove it. MYSQL_ADVISOR_DASHBOARD=false turns it off.

Reading the findings with care

• Every query goes through one guard that refuses anything other than a single read statement, and the fixes are only shown, never run.
• Counters restart with the server: until it has been up 7 days (unused_index_min_uptime_days), unused indexes are only notices.
• Counters are per server: an index unused on the primary may serve reads on a replica, so run the report there too.
• Index suggestions for full scans index the columns compared with = ?; confirm with EXPLAIN first.
• A check that cannot run is reported as skipped with its reason; one broken check never breaks the report or the dashboard.

Support

Found a bug or want a feature? Open an issue on GitHub. For help adding it to your own project, use the quote form above.