How to optimize this SQL query?

Question

I have a database with questions and answers that get translated into all languages that site is used by. But when a question is not translated yet I want to show that question in english language. So a gettext-like behavior.

My current SQL query for this looks like this:

SELECT * FROM questions_view WHERE `language` = "de" AND `#parent` IS NULL
UNION 
SELECT * FROM questions_view WHERE `language` = "en" AND `#parent` IS NULL 
    AND id NOT IN (SELECT id 
                   FROM questions_view 
                   WHERE `language` = "de")

But I feel like this is not the optimal way of doing this. Any tips?

Answer 1

Hm. I can't think of any other solution. You might set the language to null if a question is not yet translated, which would allow you to modify as follows:

select * from questions_view where `language` = "de" and `#parent` is null
union 
select * from questions_view where `language` is null and `#parent` is null

OTOH it might help to first add the translated questions to a temp table and then perform the "does not exist in German"-check as

and not exists (select 1 from temp_translated t where t.id = id)

Answer 2

This:

SELECT  qi.*
FROM    (
        SELECT  DISTINCT id
        FROM    questions_view
        ) qd
JOIN    questions_view qi
ON      qi.id = qd.id
        AND qi.language =
        COALESCE(
        (
        SELECT  language
        FROM    questions_view qn
        WHERE   parent IS NULL
                AND language = 'de'
                AND qn.id = qd.id
        ),
        (
        SELECT  language
        FROM    questions_view qn
        WHERE   parent IS NULL
                AND language = 'en'
                AND qn.id = qd.id
        )
        )

or this:

SELECT  COALESCE(qde.question_text, qen.question_text)
FROM    (
        SELECT  DISTINCT id
        FROM    questions_view
        ) qd
LEFT JOIN
        questions_view qde
ON      qde.id = qd.id
        AND qde.language = 'de'
LEFT JOIN
        questions_view qen
ON      qen.id = qd.id
        AND qen.language = 'en'

Which if these queries is better depends on you database system and on how many questions in your database are translated.

See this series of articles in my blog for more detail:

• Fallback language names: Oracle

Answer 3

Don't know if I am correct, but isn't this enough

select * from questions_view where language in ('de','en') and #parent is null

Answer 4

Possibly remove 1 trip to the DB by removing the exists and just taking the first available answer, e.g.

Select Top 1 *
FROM
(
    select 1 as myRank, * from questions_view where `language` = "de" and `#parent` is null
    union 
    select 2 as myRank, * from questions_view where `language` = "en" and `#parent` is null 
) A
Order By myRank asc

Answer 5

I'd go for danish's answer, furthermore, rather than wildcarding the query, only return the column name's you require rather than retrieving them all.

eg

Select col1, col2, col3, col_etc from questions_view where 
language in ('de','en') and #parent is null