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:
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) )
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
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