Should database be denormalized if I need an index across multiple tables
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)