This is probably the noobiest SQL question ever, and I'm almost positive there's a JOIN
or something in PostgreSQL for this, but I can't find it so I'm using a naive method. Here's a bit about my schema:
phrases:
CREATE TABLE phrases
(
phrase_id bigserial NOT NULL,
language character varying(4) NOT NULL,
text text NOT NULL,
CONSTRAINT phrases_pk PRIMARY KEY (phrase_id)
)
translations:
CREATE TABLE translations
(
source_id bigint NOT NULL,
destination_id bigint NOT NULL,
CONSTRAINT translations_pk PRIMARY KEY (source_id, destination_id)
)
I use the following query to get 100 phrases (for two language pairs). This requires limiting the results to the language pairs, then finding their translations in the translations
table:
SELECT P1.phrase_id, P1.language, P1.text, P2.phrase_id, P2.language, P2.text
FROM phrases P1, phrases P2, translations T
WHERE (P1.language = 'eng' AND P2.language = 'fra'
AND P1.phrase_id = T.source_id
AND P2.phrase_id = T.destination_id)
LIMIT 100
For what it's worth, the query runs in 34 ms with ~7.5m rows in the translations
table, and 1m rows in the phrases
table. I'll end up doing full text searches on the phrases table though, so I want to make the translation lookup as efficient as possible (as the translations
table will grow ~5x faster than phrases
). Lookup in translations
is also the most common operation in this database.
Are there any fancy methods of accomplishing this more elegantly/efficiently in PostgreSQL?
P.S. - There is no foreign key constraint in the translations
table because I am still in the process of sanitizing the database and removing old records. Importing into Postgres was impossible with the FK due to entries missing from phrases
.