Re: How to speed up this "translation" query?

On 1 aug 2006, at 20.09, tlm wrote:
> SELECT q3.translation, q2.otherstuff
> FROM
> (
> SELECT INPUT.word, q1.meaning_id, INPUT.otherstuff
> FROM
> INPUT
> INNER JOIN
> (
> SELECT translation, meaning_id
> FROM TRANS
> WHERE translation IN (SELECT word FROM INPUT)
> ) AS q1
> ON INPUT.word = q1.translation
> ) AS q2
> LEFT JOIN
> (
> SELECT translation, meaning_id
> FROM TRANS
> WHERE language_id=5
> ) AS q3
> ON q2.meaning_id=q3.meaning_id;

Maybe I'm not following you properly, but I think you've made things
a little bit more complicated than they need to be. The nested sub-
selects look a little nasty.

Now, you didn't provide any explain output but I think the following
SQL will achieve the same result, and hopefully produce a better plan:

SELECT t2.translation, i.otherstuff
FROM input i INNER JOIN trans t ON i.word=t.translation
INNER JOIN trans t2 ON t.meaning_id=t2.meaning_id
WHERE t2.language_id=5;

The query will also benefit from indices on trans.meaning_id and
trans.language_id. Also make sure the tables are vacuumed and
analyzed, to allow the planner to make good estimates.

Sincerely,

Niklas Johansson