Laravel Mysql Explain Laravel Package

Getting Started

composer require tpetry/laravel-mysql-explain

Nothing to publish, no config file — the MySqlExplainServiceProvider is auto-discovered and binds Tpetry\MySqlExplain\MySqlExplain as a singleton. The entire public surface is two methods on that class:

use Tpetry\MySqlExplain\MySqlExplain;

app(MySqlExplain::class)->explain('SELECT * FROM orders WHERE user_id = 42');
app(MySqlExplain::class)->explainOrFail('SELECT * FROM orders WHERE user_id = 42');

Where to look first: open vendor/tpetry/laravel-mysql-explain/src/MySqlExplain.php. It is ~50 lines: it prepends EXPLAIN FORMAT=JSON to your SQL, runs it via DB::select(), and hands the JSON to tpetry/php-mysql-explain, which renders an ASCII tree. Reading it takes two minutes and removes all mystery about connection handling and failure modes.

First use case: the canonical "why is /orders slow in staging" loop. Grab the offending statement from storage/logs/laravel.log or the slow query log, then in Tinker:

>>> app(\Tpetry\MySqlExplain\MySqlExplain::class)->explain('select * from orders where user_id = 42');
└─ Index lookup on orders using orders_user_id_index (user_id=42)  (cost=1.2 rows=14)

That tree — nested loops, index lookups, table scans, filters, sort steps — is the whole point. It is the EXPLAIN FORMAT=JSON output flattened into a shape you can paste into a PR comment or a Slack thread without asking a DBA to decode query_block.select_id.

Prerequisites: MySQL 5.6.5+ or MariaDB 10.1+ (JSON EXPLAIN); MySQL 8 / MariaDB 10.5+ give the richest output. It does not work on SQLite or Postgres.

Implementation Patterns

Artisan command for repeatable investigation. The highest-leverage thing you can build in 20 minutes:

class ExplainQuery extends Command
{
    protected $signature = 'db:explain {sql} {--fail}';

    public function handle(MySqlExplain $explain): int
    {
        $this->line($this->option('fail')
            ? $explain->explainOrFail($this->argument('sql'))
            : $explain->explain($this->argument('sql')));

        return self::SUCCESS;
    }
}

Now php artisan db:explain "select ..." is a one-liner in your terminal history and the output is copy-pasteable into tickets.

Before/after diffs. Because the return value is a plain string, the most useful workflow is: explain the old query, save output, apply the index migration, explain the new query, diff the two files. The tree structure makes the change glaring — a Table scan on orders node disappearing under a Nested loop inner join is a visible win you can attach to the PR.

Guarding hot paths in tests. explainOrFail throws instead of returning when the plan is degenerate (e.g. a full table scan with no usable index). Wire it into an integration test that runs against a seeded MySQL database and covers your top five queries:

public function test_orders_index_uses_index(): void
{
    app(MySqlExplain::class)->explainOrFail(
        'select * from orders where user_id = 42 order by created_at desc'
    );
    $this->assertTrue(true);
}

This is cheap regression protection against a future migration silently dropping orders_user_id_index. Treat it as a smoke test, not a correctness test.

Dev-only route. A GET /_explain?sql=... route registered behind app()->isLocal() lets frontend developers self-serve query plans. Gate it on abort_unless(app()->isLocal(), 404) and never register the route in RouteServiceProvider outside the local branch.

Pair with the slow query log, not with code reading. The realistic day-to-day loop is: DB::listen() in a local service provider to log anything over 100ms → reproduce the slow endpoint once → feed the logged SQL into Tinker or the command above. EXPLAIN is a plan, not a profile — it tells you how the optimizer intends to execute, so combine it with actual timings from your slow log rather than guessing.

Gotchas and Tips

No parameter binding support. The API takes a raw SQL string and prepends EXPLAIN FORMAT=JSON. You cannot pass a Builder, and ? placeholders will fail at the MySQL level. You must interpolate values yourself — 'select * from orders where id = ' . (int) $id. That is fine for a debugging tool but means you should never let user input reach it, even in dev.

It uses the default connection. There is no connection argument. If the slow query ran on a replica or a secondary connection, swap the default first with DB::setDefaultConnection('replica') (restore it afterwards) or the plan will reflect the wrong schema and different statistics. Plan differences between primary and replica are real and misleading.

It will not work in a SQLite-based test suite. The package executes raw MySQL syntax. If your CI matrix runs phpunit against SQLite in-memory (very common in Laravel apps), any test touching MySqlExplain will error. Isolate those tests in a MySQL-backed group and use $this->markTestSkipped() when DB::getDriverName() !== 'mysql'.

explainOrFail is opinionated. It throws on plans that perform table scans without index usage. That's useful as a CI signal for hot queries, but plenty of legitimately fast queries scan a 12-row lookup table. Don't blanket-apply it to every query in your application — pick the queries where a scan is actually a defect, or you'll be arguing with it about seed tables.

Output is human-readable, not machine-readable. The ASCII tree is not a stable contract; don't assertStringContainsString on deeply nested node text across package upgrades. If you need structured assertions, bypass the Laravel wrapper and call tpetry/php-mysql-explain directly, or run DB::select('EXPLAIN FORMAT=JSON ...') and inspect the JSON yourself. The wrapper is for humans; the JSON is for machines.

Large queries produce very large trees. Derived tables, UNIONs, and CTEs explode into dozens of nodes with subquery aliases (<derived2>, <subquery3>), and each nested block re-indents. The output often scrolls past a terminal buffer or gets truncated in Slack. Pipe to | less or write to a file. When reading, don't start at the top — scan for Table scan and Using filesort and work upward.

The plan depends on statistics that may be stale. EXPLAIN reflects whatever the optimizer currently believes, which is populated by ANALYZE TABLE. If a plan looks irrational on a staging copy restored from production, ANALYZE TABLE orders first before concluding the index is missing. This bites people who compare a fresh production plan against a stale staging dump.

MariaDB output is close but not identical. MariaDB supports EXPLAIN FORMAT=JSON but the JSON schema differs from MySQL 8's in places, so the rendered tree can omit cost estimates or mislabel node types. If you're on MariaDB, sanity-check a simple single-table query first so you know what "normal" looks like on your platform.

It doesn't execute the query — mostly. EXPLAIN on a SELECT is plan-only and safe, so don't hesitate to run it against read replicas, including near-production ones. But EXPLAIN ANALYZE (MySQL 8.0.18+) actually runs the statement, and this package does not use it. If you need real timings, you'll run EXPLAIN ANALYZE yourself — never against production, and never on an INSERT/UPDATE/DELETE, because those do execute what they analyze.

Extension point is thin, by design. There's no config file, no macro registration, no formatting hooks. If you want coloured output or a JSON renderer, you write a small adapter around DB::select('EXPLAIN FORMAT=JSON ...') yourself. The package's value is the ~20 lines that turn JSON into a readable tree; everything else is yours to build.