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
