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
