Change UI lookup joins to inner join when field is mandatory

I wouldn’t say this is an index usage issue, but it is very likely that the optimizer will/must perform the sort later in the query plan when using a left join. After the left-join scanned set has been merged into the main table scanned set. 

When using an inner join, the optimizer may choose (depending on table statistics) to first scan the look-up table, sort it and then seek the main table records based on the sorted look-up table records. This strategy will probably be chosen when the joined table is very small compared to the selected table.

And yes, this will help performance when the main table is very small, as it can cherry-pick that first page of records instead of having to sort the entire table before limiting the set to the first number of records.

However, inner-join strategy is a bit trickier than it seems.

There is no guarantee that the value of the mandatory column will actually result in a record when performing an inner join. This would require the look-up reference to be a checked reference. However, the look-up reference may not be checked for various reasons. Most often when there is a 'covering' look-up reference. For example, a look-up between sub_project_activity and project does not require the reference to be checked when it is implicitly checked by the reference between sub_project_activity and sub_project, when the reference between sub_project and project is also checked.

Furthermore, there is no guarantee that the mandatory column is actually filled when the queried entity is a view. The result would be that records would be missing from the result where the 'mandatory' column is not filled. 

The challenges of two situations above are compounded by look-up display resolvement, when multiple 'hops' are required to reach the translation table. Even when the mandatory look-up column is filled and the reference is checked, any additional step required to reach the translation may result in a missing record, causing the record to be omitted from the inner join.

In a production environment, having records disappear due to the sorting could be disastrous. Forcing an inner join by default for mandatory columns or even opt-in via a setting would be a very dangerous feature.