Claudio Ortolina
48 lines
1.5 KiB
Elixir
48 lines
1.5 KiB
Elixir
defmodule MusicLibrary.Repo.Migrations.AddScrobbledTracksIndexes do
|
|
use Ecto.Migration
|
|
|
|
def change do
|
|
# Helps GROUP BY in get_top_albums / get_top_albums_by_days
|
|
# Also helps the WHERE json_extract(album, '$.title') != '' filter
|
|
execute(
|
|
"""
|
|
CREATE INDEX scrobbled_tracks_album_title_artist_name_index
|
|
ON scrobbled_tracks(
|
|
json_extract(album, '$.title'),
|
|
json_extract(artist, '$.name')
|
|
)
|
|
""",
|
|
"DROP INDEX scrobbled_tracks_album_title_artist_name_index"
|
|
)
|
|
|
|
# Helps GROUP BY in get_top_artists / get_top_artists_by_days
|
|
execute(
|
|
"""
|
|
CREATE INDEX scrobbled_tracks_artist_name_index
|
|
ON scrobbled_tracks(json_extract(artist, '$.name'))
|
|
""",
|
|
"DROP INDEX scrobbled_tracks_artist_name_index"
|
|
)
|
|
|
|
# Helps LEFT JOIN to collection/wishlist subqueries
|
|
# (join condition: cr.release_id == json_extract(t.album, '$.musicbrainz_id'))
|
|
execute(
|
|
"""
|
|
CREATE INDEX scrobbled_tracks_album_musicbrainz_id_index
|
|
ON scrobbled_tracks(json_extract(album, '$.musicbrainz_id'))
|
|
""",
|
|
"DROP INDEX scrobbled_tracks_album_musicbrainz_id_index"
|
|
)
|
|
|
|
# Helps LEFT JOIN to artist_infos
|
|
# (join condition: ai.id == json_extract(t.artist, '$.musicbrainz_id'))
|
|
execute(
|
|
"""
|
|
CREATE INDEX scrobbled_tracks_artist_musicbrainz_id_index
|
|
ON scrobbled_tracks(json_extract(artist, '$.musicbrainz_id'))
|
|
""",
|
|
"DROP INDEX scrobbled_tracks_artist_musicbrainz_id_index"
|
|
)
|
|
end
|
|
end
|