7

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.

Skeets
  • 4,476
  • 2
  • 41
  • 67
  • 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. – Jerodev Jan 30 '20 at 07:35

2 Answers2

11

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();
Robin Bastiaan
  • 572
  • 2
  • 8
  • 21
Adriaan Zonnenberg
  • 161
  • 2
  • 9
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);
TsaiKoga
  • 12,914
  • 2
  • 19
  • 28
  • 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. – Skeets Jan 30 '20 at 08:00
  • 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. – Skeets Jan 30 '20 at 08:00
  • 1
    @Skeets I have updated the answer for the string value. – TsaiKoga Jan 30 '20 at 09:03