XF Finder join index hints

Xon

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

ie;
SQL:
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.
 
Last edited:

kick

Active member
+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.
 
Top