How to execute an `explain select` on a laravel builder

Question

I know I could get the raw query from the query log, paste in all the bound variables (also found in the query log), slap a explain at the front of the query, and run it directly in the mysql console to get the explanation for the query.... but is there any quicker way to get the explanation?

Ideally, I'd like to do something like this:

$query = User::where("favorite_color", "blue");

dd($query->explain());

(obviously, the actual query is going to be much more complicated and have some joins)

I tried adding on the explain like this:

$query->selectRaw("explain select user.*");

But this resulted in a query that started with:

select explain select...

... which is just invalid sql.

Abut

as far as I know, there is no function to do this. You will have to create the query using the query builder and prepend EXPLAIN manually. Then you can manually execute this query. – Gurrola

Answer 1

As of Laravel 8.12, you can simply call ->explain() on the query builder, like you described in your question. Or use ->explain()->dd() to die & dump the explanation.

Example:

User::where("favorite_color", "blue")->explain()->dd();

Latecomer

Answer 2

Firstly, change the code to raw sql,

Secondly then add explain before the raw sql,

And use DB::select()

$query = User::where("favorite_color", "blue");
$bindings = collect($query->getBindings())->map(function($q) {
    return is_string($q)? "\"$q\"": $q;
})->all();
$sql_with_bindings = str_replace_array('?', $bindings, $query->toSql());
DB::select("explain ".$sql_with_bindings);

Lanate

This is not as elegant as I was hoping, but it seems that there's no way to do this without converting the builder into raw SQL. – Abut

It's worth noting that the values in $query->getBindings() need to be wrapped in quotes, or any bound variables with white space in them will generate bad SQL. – Abut

@Abut I have updated the answer for the string value. – Lanate