4

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?

Reference Table:

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.

10
  • 2
    What is wrong with lower(album.name) = ? and lower(song.name) = ? – Lennart Jan 6 '15 at 7:59
  • 5
    If you don't update your tables too often (or can refresh the materialized view often enough), you can create a materialized view from the two tables and create the index on it. – dezso Jan 6 '15 at 10:00
  • 4
    Well, it is duplicated, yes. The difference is that you don't have to maintain the denormalization logic, just keep refreshing the MV. If the tables are updated from batch jobs, this is probably the easiest way to go. – dezso Jan 6 '15 at 10:19
  • 2
    @dezso: I agree with your materialized view suggestion. How about you add it as an answer? – Jon of All Trades Feb 10 '15 at 21:43
  • 1
    If users are doing free-form entry of text, a regular index likely isn't going to be much help to you. If you want someone to be able to type in "Breaking the Habit" or "Meteora" or "Breaking Habit" and get the result, then at a minimum you're going to be ending up with a LIKE query with a leading wildcard and that would prevent an index from being used even if everything was in a single table. You're probably looking at full text search postgresql.org/docs/8.3/static/textsearch-intro.html which is where the document is constructed from multiple tables. – Justin Cave Jun 9 '16 at 20:16
1

If you are getting the LOWER('Meteora Breaking The Habit') portion of the query from a source that it outside your control, then I would recommend keeping your song/album table structure (although remove album_id from song table), but include a 3rd table:

CREATE TABLE ALBUM_SONG (
  album_id,
  song_id,
  album_song_name -- this would be album.name || song.name
)

This is essentially a join table between the two for M-M relationships ('cause hey - the same song could be on multiple albums)..

Then you can index and query the join table on LOWER(album_song_name)

0

My advice and experience - do not denormalise your database. Instead redesign it, and use 1NF, 2NF and 3NF at the very least. I am not too familiar with the tables you are using. Consider the PK seems to be the same in both. Should the PK in album be the same as the PK in song? I don't think there is a need for these here as song is dependent on album, and I think these should be in the one table. The album and song are only partially dependent on the artist, so it is the artist who should have an identified in a table, and these should be linked to album id in another table? I've just finished my degree and starting to work with relational databases so hoping to help out here as well as learn.

1
  • 4
    But the data is normalized. The user query is not. – paparazzo Nov 11 '15 at 15:04

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.