Archive for August 8th, 2009
This is a series of articles on efficient querying for a localized name, using a default (fallback) language if there is no localized name:
A quick reminder of the problem taken from Stack Overflow:
I have table
itemand another table
languagewhich contains names for the
itemsin different languages:
item language data
How do I select a French name for an item if it exists, or a fallback English name if there is no French one?
We basically have three options here:
COALESCEon the results of two
- Use the combination of methods above: a
LEFT JOINfor French names and a subquery for English ones
Efficiency of each of these method depends of the fallback probability (how many items are covered by the localization).
If the localization is poor and but few terms are translated into the local language, the probability of the fallback is high. I took Latin language as an example for this.
If almost all terms are translated, the probability of fallback is low. In this case, I took French as an example (as in the original quiestion), since it is widely used and localizations are likely to cover most terms.
In previous articles I shown that in both Oracle and SQL Server, the second method (two
LEFT JOINs) is more efficient to query poorly localized languages, while for well-localized languages the third query should be used, i. e. a
LEFT JOIN for the local language and a subquery for the fallback one.
Now, let’s create sample tables and see how these queries behave in PostgreSQL:
Read the rest of this entry »