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?

Code coding practice avoids using the * wildcard. You're building traps for yourself if you use it. — markus
– markus, Commented Oct 12, 2009 at 10:49

Quassnoi · Accepted Answer · 2009-10-12 10:39:02Z

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

edited Oct 12, 2009 at 10:39

answered Oct 12, 2009 at 10:33

Quassnoi

427k94 gold badges628 silver badges623 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Sebastian Hoitz Over a year ago

Thank you for your series on this. The MySQL article provides a good solution.

Thorsten Dittmar · Accepted Answer · 2009-10-12 10:32:22Z

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)

answered Oct 12, 2009 at 10:32

Thorsten Dittmar

56.9k10 gold badges95 silver badges145 bronze badges

Comments

danish · Accepted Answer · 2009-10-12 10:34:23Z

1

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

answered Oct 12, 2009 at 10:34

danish

5,6002 gold badges27 silver badges28 bronze badges

1 Comment

Paddy Over a year ago

This would get everything in German and English, regardless of whether a translation existed or not.

Andrew · Accepted Answer · 2009-10-12 10:36:03Z

1

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

answered Oct 12, 2009 at 10:36

Andrew

27.4k5 gold badges67 silver badges90 bronze badges

Collectives™ on Stack Overflow

How to optimize this SQL query?

4 Answers 4

1 Comment

Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related