[mythtv-users] 0.25 SQL CPU Load issues after optimisation

[Gary Buhrmaster]

On Thu, Jul 19, 2012 at 7:50 PM, Another Sillyname
<anothersname at googlemail.com> wrote:
....
> I'm trying to reverse engineer the SQL command that appears to be the
> trigger/cause of the the problem but I'm beyond the limit of my
> skillset on SQL here.

Well, I have not tried this (I am not in a position to test at the moment),
but you may be able to just take the sql you have identified, and
use the mysql cli, type in 'EXPLAIN ' and then copy/paste everything
starting with the SELECT (probably need to add a trailing ';'.  And
then try EXPLAIN EXTENDED.

That should produce the query plan.  There is lots of documentation
as to how to read the output, but if you do not do sql for a living,
it may not be clear what could be done to improve the query.
It *might* be interesting to see the same output from someone who
does not experience the issue.

Just to be sure your table statistics are correct, use the mysql cli,
and do a "show variables list '%metadata%';" and make sure
the response includes a "stats_on_metadata" ON, and then
do a "show table status;"  That will update any database
statistics, allowing mysql to choose an "optimal" query plan.