How to optimize this SQL query?

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 en开发者_开发百科glish 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?

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

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)

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

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