MySQL : ORDER BY FIELD / GROUP BY

Question

My question is about MySQL optimisation and good practice.

I have to get translated text list with order preference : if I can't find text in english, I want to use french text and if it does not exists, I want to use spanish.

In other words, I have :

id  lang text
1   fr   text_FR
1   en   text_EN
1   es   text_ES
2   fr   text_FR2
3   fr   text_FR3
3   es   text_ES3

And I want:

id  lang text
1   en   text_EN
2   fr   text_FR2
3   fr   text_FR3

So I read some topics like this, this, that, or that. Ok. I tried this :

SELECT text FROM (
    SELECT id, text 
    FROM translation 
    ORDER BY FIELD(lang, 'en', 'fr', 'es')
) tt GROUP BY tt.id;

But, when I EXPLAIN this query, I can read :

id  select_type table   partitions  type    possible_keys   key key_len ref rows    Extra
1   PRIMARY <derived2>  NULL    ALL NULL    NULL    NULL    NULL    4   Using temporary; Using filesort
2   DERIVED translation NULL    ALL PRIMARY PRIMARY 52      1641        Using filesort

So it's not optimized, because of the subquery. Can I avoid this subquery ?

Answer 1

I would suggest JOINing on a table by id for each language. You can use a LEFT JOIN to allow for null results. The query will still use temporary/filesort because it has to scan the entire table (you can use a WHERE to limit it more), but only for the first table. Lookups on the joined tables should be quick.

SELECT
  COALESCE(en.text, fr.text, es.text, any.text)
FROM
  f any
  LEFT JOIN f en ON (any.id = en.id AND en.lang = 'en')
  LEFT JOIN f fr ON (any.id = fr.id AND fr.lang = 'fr')
  LEFT JOIN f es ON (any.id = es.id AND es.lang = 'es')
GROUP BY any.id;

http://sqlfiddle.com/#!2/baafc/1