#241 explain in mysql debug

[Mao]

Hi,
I think this is a good idea for a more effective debug
system build in xoops: add the ability to do an explain
on the fly the mysql queries generated from xoops.

.. so maybe module's devs will start to use indexes in
their sql tables :P

banned,

[Skalpa Keo]

Logged In: YES
user_id=882380

Yeah, you're right I think, but thinking that this will actually make a difference in
the way modules are made is being naive ;)
AFAIK, a lot of modules are made by people who don't care about doing something good,
but focus more on making as many modules as possible as quickly as possible, to gain
easy popularity... and the ones who really care about the quality of their work already
use "explain" even if it's not part of the debug output.

Anyway, the idea is interesting, so I'm setting this one to "accepted", in the hope
somebody does it before I actually get the time for it (why not you in fact ? :P Isn't
that something you could actually do and send us ? )

cheers,

skalpa.>

[Jan Pedersen]

Logged In: YES
user_id=841117

I can see the use of this, but how should it be and where
should the code go?

I'm thinking a link in the debug list, but where to? It'd
have to be somewhere in the System module as the debugger is
core (or does it? Can a module replace the debugging
output?) but should this be a new area? And if so, shouldn't
more database management things be put in there? I'm
thinking things like checking and repairing the database tables.

[Skalpa Keo]

Logged In: YES
user_id=882380

Well, if a link was used it could open a new kind of popup handled like the popup debug
mode, or the smilies/avatars (by misc.php).
Now, I'm not sure adding a link is the best thing to do: this would require people to click
on the link to see the result of explain, and nobody would do it...
IMO it would be best to have this done directly in the logger output: I actually separated
the logger class from the logger rendering code (logger_render.php) on purpose, thinking of
toys like that. Since 2.0.14, the rendering code is only included when it's really needed so
it could become 10 times bigger, we wouldn't care.
So I guess the best thing to do would be this:
- Make the part that render queries check if it's a "SELECT"
- If so, execute an "EXPLAIN $query"
- If the "EXPLAIN" contains at least 1 NULL "possible_keys", ensure the query will be
displayed in bold or red.
- Render the result of the "EXPLAIN" in a div, hidden by default, after the query itself
- Add a small JS thingie to show/hide the explain result

Actually, we should even display a message like "INEFFICIENT QUERY !!!" next to bad ones...
like this, users who see the message will just bother devs until they fix their code, which
would be a positive thing :-P

[Mao]

Logged In: YES
user_id=1183441

Hi,
Look at this :)

I'm not sure if this is the correct way - I do the queries
directly in the logger_render, without making a new method
in the logger class.. dunno if you want to make one - but it
works.
I've added a visual signal when an EXPLAIN give back a
correct JOIN type or not under the type column (green light
is okey, the orange one means that something is incorrect
within the use of indexes).
.. I've added, also, a 'global $xoopsDB; under the dump
method in the main XoopsLogger class - without I cannot
query the database.

See the attached file.
banned,

[Mao]

MySql Explain