The MultiValue fields could be used for some specific field like the product name.

About msg16276, it will be good to have the query explanation to see where is spent the resources.

I think the auto-completion should probably start only after 3 chars.
About the coalesce, the problem is that we show the default language value in
the client in case of missing translation but of course it is for non fully
translated application which should probably not be considered as the norm.

We currently have an issue where a simple search (%b%) on the rec_name of the
product table (with nearly 300k records) is pretty slow because the translation
table enters in the game.

I made some tests with different schema structure / way to query the database.
In order to have a large amount of data I used /usr/share/dict/words to have a
lot of rows.

Redesigning the translation schema does not change much the performances, the
most important performance gain I obtained was by changing the way the query is
structured (instead of a "in (select …)" I used a join) but those performance
improvement where not really good (≃ 15-20%).

I also tried using a trigram indexes but they began being usefull once the
length of string searched is ≥ 3 and they require to stop searching also in the
original table (no use of the COALESCE).

Event if at first I though that dropping the coalesce was a bad idea, in the end
I guess that user that insist on using another language then the default one do
not want to see another language in their interface for fields that are
translatable.

As issue2283 but for translation table.