Confirmed Template title search is slow on large installations

[Painbaker]

Affected version

2.3.13

Template title search unnecessarily joins full template records for entries that do not match.

Suggested fix: Filter by xf_template_map.title before joining the matching templates.

SELECT `xf_template_map`.*, `xf_template_Template_1`.*, `xf_addon_AddOn_2`.*
FROM `xf_template_map`
INNER JOIN `xf_template` AS `xf_template_Template_1` ON (`xf_template_Template_1`.`template_id` = `xf_template_map`.`template_id`)
LEFT JOIN `xf_addon` AS `xf_addon_AddOn_2` ON (`xf_addon_AddOn_2`.`addon_id` = `xf_template_Template_1`.`addon_id`)
WHERE (`xf_template_map`.`style_id` = 1) AND (`xf_template_map`.`type` = 'public') AND (CONVERT (`xf_template_Template_1`.`title` USING utf8mb4) LIKE '%test%')
ORDER BY CONVERT (`xf_template_map`.`title` USING utf8mb4) ASC

Run Time: 1.808825
Select Type    Table    Type    Possible Keys    Key    Key Len    Ref    Rows    Extra
SIMPLE    xf_template_Template_1    ALL    PRIMARY                   3890    Using where; Using temporary; Using filesort
SIMPLE    xf_template_map    ref|filter    style_id_type_title,template_id,type_title    template_id|style_id_type_title    4|26    xenbaker_demo.xf_template_Template_1.template_id    1 (47%)    Using where; Using rowid filter
SIMPLE    xf_addon_AddOn_2    eq_ref    PRIMARY    PRIMARY    52    xenbaker_demo.xf_template_Template_1.addon_id    1     

    XF\Db\Mysqli\Statement->execute() in src/XF/Db/AbstractAdapter.php at line 96
    XF\Db\AbstractAdapter->query() in src/XF/Mvc/Entity/Finder.php at line 1461
    XF\Mvc\Entity\Finder->fetch() in src/XF/Admin/Controller/TemplateController.php at line 346
    XF\Admin\Controller\TemplateController->actionSearch() in src/XF/Mvc/Dispatcher.php at line 364
    XF\Mvc\Dispatcher->dispatchClass() in src/XF/Mvc/Dispatcher.php at line 266
    XF\Mvc\Dispatcher->dispatchFromMatch() in src/XF/Mvc/Dispatcher.php at line 121
    XF\Mvc\Dispatcher->dispatchLoop() in src/XF/Mvc/Dispatcher.php at line 63
    XF\Mvc\Dispatcher->run() in src/XF/App.php at line 2840
    XF\App->run() in src/XF.php at line 850
    XF::runApp() in admin.php at line 15

XF/Filterer/Template.php

case 'title':
    $finder->searchTitle($value);
    return true;

XF/Finder/TemplateMapFinder.php

public function searchTitle($match)
{
    if (strlen($match))
    {
        $this->where(
            $this->columnUtf8('title'),
            'LIKE',
            $this->escapeLike($match, '%?%')
        );
    }

    return $this;
}