Feature: Explain database queries

[sepehr]

Would be wonderful if we had the chance to be able to see the explanation for each query in the database tab.

[itsgoingd]

Hey, this is definitively a good idea, but might have to wait for some further future release. Currently we can only pre-compute the explains, which would be unnecessary in most cases and slow. We might be able to do it the way xdebug profiler is implemented in 3.0, but I will need to play with this a bit.

[sepehr]

Thanks for considering this.

TBH I'm not following what you're suggesting regarding xdebug profiler 3.0. The only feasible way I can see here, is to repeat the query prepended with explain. Just as debugbar does it.

Yes, we might end up dispatching a lot of "extra" queries to the database which is, in fact, the price for the convenience we get in our development environment. Personally fine with me.

[itsgoingd]

Decided to skip this one for now.

Ideally this would work in a way where you would click on a button for a particular query to run explain for that query, which is beyond the scope of Clockwork atm.

The only way to implement this would be to run duplicate EXPLAIN queries for every executed query ever, which seems pretty wasteful and noisy.

That all for a just little bit of convenience, since you can easily copy the query from Clockwork to your favorite sql client and run the query there. I will add a single-click copy query button, should be pretty useful in general.

I still think this is a good idea though, might revisit for a later release if there is more demand for this.

[staabm]

Today I came to the same conclusion as the intial reporter: having a button to trigger a explain query and see the result would be very valueable.

Ideally this would work in a way where you would click on a button for a particular query to run explain for that query, which is beyond the scope of Clockwork atm

thats exactly how I imagined the feature to work.. could you elaborate why this is considered out of scope?

[itsgoingd]

There's a lot of tricky problems in that implementation, just off the top of my head:

• A new Clockwork api would need to be designed to send "commands" from the Clockwork app to the inspected php app. (Currently we can only read collected data.)
• How do we re-compose the EXPLAIN query? We would need to collect the original raw query and bindings separately.
• How do we prevent SQL injection?
• How do we even prevent against running random queries? We are basically opening an api to run any sort of query in your app. :)
• How do we run the query? In Laravel we could probably get a database connection from the framework, but what do we do in a vanilla app?

This doesn't mean these issues are unsolvable ofc, but require quite some effort to solve.

[jaulz]

Just came about this as well and it would be super handy to see the EXPLAIN statement below the actual statement (instead of going the complicated way via copy/paste or the way you suggested via toggle). If we let users toggle the behaviour via a configuration option, everyone can decide if he wants to use it or not. In my special case I just would like to know if any of my queries does not use any index.