Implemented XF Finder join index hints

[Xon]

The XenForo finder system allows index hints applied to the 'from' clause, but not when doing joins.

ie;

select *
from a
join b as c use index(d) on c.id = a.id

This is desirable as at some optimization levels recent MySQL version do not correctly select the right index (or ignore it entirely!) for a join.

This would ideally result in a change to the "join" with clauses so the various getEntityWith statements could apply an index depending on context or just in the entity relationship definition would be useful.

 

[kick]

+1. To be precise in 5.6.3 there was already a change.
Starting with this version there are problems with queries. And mysql considers them to be slow and not optimized. Even if you bring the optimizer by setting the flags duplicateweedout = off, derived_merge = off.
That will not help at all. Only at the same time, due to these disabled options, the optimizer completely freezes. It can create a disk load of 90%. This problem is also relevant for the latest version of mysql. And most likely there will be the same problem on mariadb 10.4. Since there is a regression in 10.4.
This is not even as proposed, but a mistake. After all, if you follow the logic and the words of the developers that they are aimed clearly at MySql and at its forks.

XenForo will likely always be exclusively MySQL based. That is, it has to be without some incredibly significant changes that we just don’t want to do.

 

[Xon]

The lack of this feature can cause massive performance regressions;

Fixed - Incredibly poor performance for Latest Activity feed for MariaDb 10.4+

This is highly dependant on the user, number of followers and the site's activity. ~19.4 seconds vs 0.001 seconds is a serious performance regression. analyze is like explain but executes the query and reports back actual row counts. set join_cache_level=2; analyze SELECT `xf_news_feed`.*...

xenforo.com