Consider the following table example:
| id | lang_from | lang_to |
|---|---|---|
| 1 | chi | eng |
So the table contains information about translations, the translation has a direction (from and to), but when I do search the direction is not important:
WHERE (lang_from = 'chi' AND lang_to='eng') OR (lang_from = 'eng' AND lang_to='chi')
Is this an efficient way for the lookup?
P.S. I don't have indexes set, as long as it would give me an extremely low selectivity.
To make the search efficient, consider an expression index of the form:
CREATE INDEX ON tbl (GREATEST(lang_from, lang_to), LEAST(lang_from, lang_to));
And a matching predicate in queries:
WHERE GREATEST(lang_from, lang_to) = GREATEST('chi', 'eng')
AND LEAST(lang_from, lang_to) = LEAST('chi', 'eng')
Or simply:
WHERE GREATEST(lang_from, lang_to) = 'eng'
AND LEAST(lang_from, lang_to) = 'chi'
Depending on the complete scenario, variations of this index may be useful. Like adding another (selective) index field.
This only makes sense in combination with a matching index - and selective enough predicates, so that the index is actually used. Else your original form is even slightly faster.
Related: