Translation table seems to be often picked before other conditions by
MariaDB's query planner.
When there is at least one other condition on a query which require a
join (ex: category-based relation, delivery-table-based date
comparison, ...) this may not be the most efficient execution plan.
Instead, put translation lookup into its own separate query, in a way
similar to security_uid lookup. This way, two separate execution plans
can be used to execute the overall operation.
One drawback is that the new query does not know of any portal_type
condition, so all possible original values are listed along with their
respective portal types.
This does not seem to affect query performance, and on most common
(and simple) cases where a translated value is used, like module
listboxes, the query planner should be able to eliminate irrelevant
entries as such queries are already expected to provide a list of
relevant portal types and to lack condition nesting.
Preserve custom related keys: these are used when selecting or sorting,
while the scriptable keys are used when applying a condition.
Note on UI tests: mass workflow transition listbox is not supposed to be
sorted. It's bad that these tests all rely on row order, and also bad
that they all check the whole listbox. They should pick the line they
care about and not check the rest - except for one test whose purpose
would be to check the whole list anyway.