amjadiqbal/laravel-mysql-advisor
A Laravel package that analyzes your MySQL setup and surfaces actionable advice on slow queries, indexes, and configuration so you can spot performance problems early and keep your database running efficiently.
mysql-advisor is a read-only MySQL 8 / MariaDB 10.6+ health checker for Laravel apps. It queries performance_schema and information_schema, produces findings, and hands you the fix as raw SQL plus a migration or Eloquent snippet. Nothing is ever executed against your schema — ReadOnlyGuard rejects anything that isn't a single SELECT or SHOW.
Minimal steps:
composer require <vendor>/mysql-advisor # check the package README for the exact vendor name
php artisan mysql-advisor:report
That's the whole first run — no assets to build, no queue worker, no migrations of its own. If you want the UI, visit /mysql-advisor in local; it is gated behind viewMysqlAdvisor and defaults to local-only.
Where to look first, in this order:
GRANT or server setting is missing. Fix those before you trust a clean report.php artisan about to confirm the package is discovered and which connection it defaulted to.A good first use case is a pre-launch audit on staging: run --only=missing-primary-key,unindexed-foreign-key,redundant-index and fix what it finds before the schema ossifies. Perf-tuning checks (buffer-pool-hit-ratio, disk-temp-tables, top-statements) are more useful against production-like traffic than a fresh staging DB.
Gate the report in CI with --fail-on, but scope it. Exit code 1 means findings, 2 means the database couldn't be inspected at all. Those are different failures and your CI script should branch on them — a 2 silently treated as "pass" is how you end up with an advisor that has been broken for three months.
php artisan mysql-advisor:report --only=unindexed-foreign-key,missing-primary-key --fail-on
case $? in
0) echo "clean" ;;
1) echo "schema findings"; exit 1 ;;
2) echo "advisor could not inspect DB"; exit 1 ;;
esac
Treat structural checks as blocking and performance checks as advisory. unindexed-foreign-key and missing-primary-key are objective; fragmentation and stale-statistics depend on workload and timing and will generate noise on a busy CI database.
Loop --connection in multi-connection apps. The flag takes one connection per invocation, so a job or CI step that iterates config('database.connections') and runs the report per connection is the natural shape. Point it at the primary, not a read replica — replica stats are a different (and often stale) story.
Use --format=json as the integration surface. It's the clean way to post a PR comment, push to Slack, or feed a dashboard. Parse findings into {check, severity, fix_sql} and render only the fix_sql block for humans — that's the part developers act on.
Schedule a daily run rather than a per-deploy run. Table size, fragmentation, and buffer pool ratio are trend metrics. One run tells you nothing; fourteen runs tell you the table grew 40% and nobody noticed. Persist the JSON and diff it.
Copy fixes into migrations deliberately. The migration/Eloquent snippet is a suggestion, not a patch. Drop it into a real migration file, review the index name and column order, and test against a production-sized dataset — adding an index to a 50M-row table in a deploy is a different operation than adding it in a seeder.
Extend visibility, not the gate. The gate is the natural place to widen access: define viewMysqlAdvisor in a service provider to allow your staging environment or an on-call role. Remember the dashboard exposes schema names and statement digests — it is read-only, not public.
Treat the skipped list as an onboarding checklist. New environment, missing PROCESS or SELECT on performance_schema, performance_schema=OFF on a managed instance — each one is a concrete infra ticket, and the package hands you the exact GRANT statement to paste.
performance_schema digests. Query-level checks (top-statements, n-plus-one, full-table-scan) will be sparse or skipped. This is documented and reported as a skip, but it's easy to read an empty findings list as "no slow queries." It isn't. Verify which mode you're in before drawing conclusions.events_statements_summary_by_digest is in-memory. After a deploy-driven failover, top-statements and n-plus-one are effectively empty. Correlate with uptime before trusting the window.unused-index is a heuristic with a blast radius. It doesn't know about indexes used by replicas, by a second application sharing the database, by FK constraint enforcement, or by queries that haven't run in this sampling window. Never drop on the advisor's word alone. The same caution applies to redundant-index: a composite whose prefix looks redundant may still be the only index satisfying a different query's sort order.ReadOnlyGuard blocks EXPLAIN too. It allows a single SELECT or SHOW — so findings are inferred from statistics, not from planned execution. A full-table-scan finding means "the digest shows rows_examined far above rows_sent," not "we ran EXPLAIN." That distinction matters when you're arguing with a colleague about whether the query is actually bad.2 explicitly. Covered above, but it's the most common way this package silently degrades into a no-op in a pipeline.OPTIMIZE TABLE locks or rebuilds. On a large table, use pt-online-schema-change or gh-ost, and never in a request-adjacent window. The package gives you the SQL; it doesn't know your traffic shape.performance_schema column and table availability differs. Expect more skips on MariaDB, particularly around statement digest detail. Read the skip reason rather than assuming parity.v0.1.0 means pin it. CLI flags, JSON shape, and check names are all plausible to change pre-1.0. Pin the version in composer.json and assert on check names in any script you write, not on ordering or message text.true unconditionally in a shared environment.--only, --connection, --format, and the exit codes. There is no documented custom-check registry in this release, so don't design around one; compose at the CLI/JSON layer instead.How can I help you explore Laravel packages today?