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

[Håkon Alstadheim]

Den 09. juni 2016 17:47, skrev 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.
Good to know, means no need to focus on this warning when trying to
improve performance :-)