Optimize getAlbumList2 API endpoint

jeffvli commented 2 years ago

Is your feature request related to a problem? Please describe.

Loading albums via the /getAlbumList2 endpoint slows down as the offset increases. Normally with pagination it probably wouldn't be a big deal, but using a client that loads all pages at once may take an extended amount of time especially with large libraries (e.g. 20k+ albums).

An example API call:

/rest/getAlbumList2?u=redacted&t=redacted&s=redacted&v=1.15.0&c=test&f=json&type=alphabeticalByName&size=500&offset=7500

Describe the solution you'd like

It would be great if the response times of the /getAlbumList2 endpoint were improved, especially at larger pagination offsets. My client (sonixd) currently doesn't have an album list pagination option so all albums are loaded at once. This means that with my library of 13.5k albums, it takes around ~9-12 seconds for the initial load of the album list.

Describe alternative solutions that would also satisfy this problem

N/a

Additional context

This is tested using Navidrome on Docker deluan/navidrome:develop.

Here is an example of loading ~13.5k albums on Navidrome:

Here is an example of loading ~9k albums on Airsonic (vanilla):

deluan commented 2 years ago

Thanks for opening this.

Found the issue, it was the same as with MediaFiles: Joining with genres (to get multiple genres) requires a group by clause that slows down the queries significantly. As multiple genres are not a thing in the Subsonic API , I just optimized the queries for it. Let me know what are the results.

Should be fixed in 9422373be0edc0652af5fdd0a09958353797d235

deluan commented 2 years ago

Also added a new index to be used when type=alphabeticalByArtist. As I said in Discord, if you are now getting times ~150ms using type=alphabeticalByName, I don't think we can get better times than that, as the SQL used by that call is pretty barebones. This is most probably an issue with SQLIte3, and not sure if we will be able to optimize it further.

Let me know your results after running the version with the new index (commit 9e48d87f8412b0dc9824557f5ad57538390e301c)

jeffvli commented 2 years ago

The optimized response times are looking a lot better! Both type=alphabeticalByName and type=alphabeticalByArtist are exhibiting similar response times now, going from ~450-500ms before the optimizations to around ~100-170ms now.

Thanks again for the quick fix.

github-actions[bot] commented 1 year ago

This issue has been automatically locked since there has not been any recent activity after it was closed. Please open a new issue for related bugs.

navidrome / navidrome