I have a catalog of album and song names. The song has a foreign key to the album table. I expect queries like:
SELECT * FROM song s JOIN album a ON a.id = s.album_id WHERE LOWER(CONCAT_WS(' ', album.name, song.name) = LOWER('Meteora Breaking The Habit')
I wish to create an index on the above type of search terms but it looks like I can't have an index across multiple tables. In this case, would it make sense to de-normalize the table and put the song & album name in a single table or is there a better approach?
CREATE TABLE album ( id SERIAL PRIMARY KEY, name text NOT NULL ); CREATE TABLE song ( id SERIAL PRIMARY KEY, name text NOT NULL, album_id integer NOT NULL, CONSTRAINT album_id_fk FOREIGN KEY (album_id) REFERENCES album (id) );
P.S. For the sake of understanding, I'm ignoring the fact that query can be song name first & album name later. Also ignoring that query can contain band name too. The query sent by the client is of the form
album.name + ' ' + song.name.