Using MySQL explain for queries in Laravel 8.x

Amit Merchant · November 16, 2020 ·

MySQL’s EXPLAIN statement is a handy tool if you want to take a quick glance over queries and to make out what’s wrong with them. Essentially, EXPLAIN is used to obtain a query execution plan (that is, an explanation of how MySQL would execute a query).

With the help of EXPLAIN, you can see where you should add indexes to tables so that the statement executes faster by using indexes to find rows. You can also use EXPLAIN to check whether the optimizer joins the tables in an optimal order.

Now, if you were using Laravel 7.x, the way of using EXPLAIN was rather tricky. You would need to call toSql() which would print the raw query, you would then copy the result, open your favorite SQL editor, prepend the query with EXPLAIN, replace all ? in the query with actual values, and check the result.

Pretty tedious, no?

The explain() method

But not anymore! With the help of this PR in Laravel 8.x, it’s now a matter of calling explain() on the query to return the explanation. Or you can use explain()->dd() to die & dump the explanation.

So, if you’d like to explain a query on the Book model, you can do it like so.

Book::where('name', 'Ruskin Bond')->explain()->dd();

This will return the result in the following format.

Illuminate\Support\Collection {#5344
    all: [
        {#15407
            +"id": 1,
            +"select_type": "SIMPLE",
            +"table": "books",
            +"partitions": null,
            +"type": "ALL",
            +"possible_keys": null,
            +"key": null,
            +"key_len": null,
            +"ref": null,
            +"rows": 9,
            +"filtered": 11.11111164093,
            +"Extra": "Using where",
        },
    ],
}

You can use this with the DB facade as well like so.

DB::table('books')->where('name', 'Ruskin Bond')->explain()->dd();

Hi there! I'm Amit. I write articles about all things web development. If you like what I write and want me to continue doing the same, I would like you buy me some coffees. I'd highly appreciate that. Cheers!