Improve the Query runtime for smf 2.1 beta 4+

[albertlast]

The mode use this old version of authority check: https://github.com/dragomano/Optimus/blob/53b348d6826cc4631da95a4793d47768f812ee04/Sources/Class-OptimusSitemap.php#L87 and here https://github.com/dragomano/Optimus/blob/53b348d6826cc4631da95a4793d47768f812ee04/Sources/Class-OptimusSitemap.php#L153

Since this pr got commited: https://github.com/SimpleMachines/SMF2.1/pull/4727

Exists a newer faster default way: https://github.com/albertlast/SMF2.1/blob/f69bc578508a728299e4def322836556cea90452/Sources/Subs.php#L6340-L6342

You code should look like this:

SELECT t.id_topic, m.poster_time, m.modified_time
FROM {db_prefix}topics AS t
    INNER JOIN {db_prefix}messages AS m ON (m.id_msg = t.id_last_msg)
    INNER JOIN {db_prefix}boards AS b ON (b.id_board = t.id_board)
WHERE FIND_IN_SET(-1, b.member_groups) != 0' . (!empty($modSettings['recycle_board']) ? ' AND b.id_board <> {int:recycle_board}' : '') . (!empty($modSettings['optimus_sitemap_topics']) ? ' AND t.num_replies > {int:replies}' : '') . ' AND t.approved = 1
ORDER BY t.id_topic

to

SELECT t.id_topic, m.poster_time, m.modified_time
FROM {db_prefix}topics AS t
    INNER JOIN {db_prefix}messages AS m ON (m.id_msg = t.id_last_msg)
    INNER JOIN {db_prefix}boards AS b ON (b.id_board = t.id_board)
WHERE EXISTS (SELECT DISTINCT bpv.id_board FROM {db_prefix}board_permissions_view bpv WHERE (bpv.id_group = -1 AND bpv.deny = 0) AND bpv.id_board = b.id_board) . 
    (!empty($modSettings['recycle_board']) ? ' AND b.id_board <> {int:recycle_board}' : '') . (!empty($modSettings['optimus_sitemap_topics']) ? ' AND t.num_replies > {int:replies}' : '') . ' AND t.approved = 1
ORDER BY t.id_topic

On my 500k test board the first/old query takes 326ms the new version 23ms.

[dragomano]

Thanks, I have implemented this now.