[mythtv-users] Bad mysql performance -- huge oldrecorded -- joins without index

[Michael T. Dean]

On 06/09/2016 10:16 AM, stinga wrote:
> On 08/06/16 14:10, Simon Hobson wrote:
>>> Mysqltuner complains about "Joins performed without indexes", but I do
>>> not know enough mysql to start looking into that. Any hints ?
>> Well that's a code thing - so without altering the code there's 
>> nothing you can do to optimise that aspect.
>> But there is great scope for just tweaking the memory footprint for 
>> MySQL. Up to some point, basically the more RAM you allow it to use, 
>> the better it will perform - the defaults are "fairly conservative".
>>
>> Overall your values look reasonable - I assume you've already been 
>> tweaking them. But this one does stand out : "Query cache is 
>> disabled" - I assume that's not normal.
> You can add an index if you want,

though messing with the database schema may prevent future database 
upgrades from occurring properly, so is strongly discouraged--if you 
want to change the schema, submit a ticket/patch, or at least start a 
discussion to get it into "upstream" code so you're not running a broken 
fork

> no code changes required.
>
but in this case, code changes are actually required to add the index as 
I'm pretty sure the "join performed without indexes" is referencing a 
join on a temporary table that's created by the code each time it's 
needed and then dropped after it's used.  I'm pretty sure that creating 
the index on the temp table that's created/used/dropped each time the 
scheduler runs (so creating the index for each scheduler run) would take 
at least as much time as just doing the join with the unindexed temp table.

I think the general "best practice" (meaning guideline before 
profiling--which should always be performed before any efforts at 
optimization) says not to create an index on a temp table if you won't 
use the temp table and index at least twice.  While I haven't looked at 
the code in quite a while to be sure, I don't think we'd use the 
table/index twice.

Mike