Currently in our UI's if you create a table or view and include this column in the sorting the UI will create a left join to the lookup table. This is required to create the proper sortation. Obviously a left join is often not preferred. It should be an inner join, otherwise the query will not use the index.
I understand that sometimes setting up this join as an inner is not possible, because if the join would return no value then the row itself would not be visible.
Therefor I think the following solution would be perfect:
If a column is not mandatory, the UI should always use left joins.
If a column is mandatory, the UI should always use inner joins. However in the SF model it is possible to still mark the column as “use left join on lookup”. This is required when the lookup itself is a view that sometimes might not return any record.
By changing this, the overall performance of all application will increase.
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.