[Bug]: Performance of querying for Newest Authors on Homepage > 2mins for big E-Book Library

[friral]

What happened?

The Newest Author load time on the Homepage of is still (v2.15) very high for my 40K E-Book Library. Smaller libraries (1-2K audiobooks) are fine. The other similar 6 queries (see log) on the same page take only very few seconds or ms.
Tested on my desktop Firefox & the Android app with same results

What did you expect to happen?

1. Load as fast as the other queries. For some reason the "Recently Added" query runs much quicker than "Newest Authors" although there are twice as many books/items than authors in the library
2. For now, quickfix to make the library usable again: Maybe make the query for the newest authors disable-able in the settings

Steps to reproduce the issue

1. Load 40k books into a book-library
2. Navigate to Homepage of that library
3. Watch the App freeze for a couple of minutes
4. Check logs for the actual time

Audiobookshelf version

v 2.15.0

How are you running audiobookshelf?

Docker

What OS is your Audiobookshelf server hosted from?

Linux

If the issue is being seen in the UI, what browsers are you seeing the problem on?

None

Logs

2024-10-14 08:49:38.557
DEBUG
Loaded 8 of 8 items for "Continue Listening/Reading" in 0.72s

2024-10-14 08:49:43.263
DEBUG
Loaded 0 of 0 items for "Continue Series" in 4.71s

2024-10-14 08:49:43.672
DEBUG
Loaded 10 of 41478 items for "Recently Added" in 0.41s

2024-10-14 08:49:43.720
DEBUG
Loaded 5 of 2254 series for "Recent Series" in 0.05s

2024-10-14 08:49:47.160
DEBUG
Loaded 10 of 34816 items for "Discover" in 3.44s

2024-10-14 08:49:47.625
DEBUG
Loaded 4 of 4 items for "Listen/Read Again" in 0.47s

2024-10-14 08:52:07.970
DEBUG
Loaded 10 of 18309 authors for "Newest Authors" in 140.34s

2024-10-14 08:52:07.974
DEBUG
Loaded 6 personalized shelves in 150.13s

Additional Notes

No response

[nichwall]

Same as #3237, but for authors instead of series. It is the same sort of query causing the slowdown. The backend data model likely needs to be changed to fix this.

Also discussed starting from #3259 (comment)

[friral]

Don't forget about the possible quickfix to just disable the query/feature or introduce a setting for that to the user. I would certainly appreciate this low-effort solution until there is time for the 'big' solution to change the data model.

Right now the app is hardly usable to me whenever I (sometimes only by accident while browsing other libraries) touch the home screen of the books library

[advplyr]

We found that an index can solve this. If you want to resolve this before that release is ready you can run the following sql statement:

create index bookAuthors_bookId ON bookAuthors(authorId);

You'll probably want to remove that before migrating to 2.16.0 though to not have 2 indexes if we use a different name. If we use the same name it will be fine.

[friral]

awesome! Good job!
Looking forward to the release

[github-actions]

Fixed in v2.16.0.

[advplyr]

If you update to v2.16.0 I'm curious how much the performance improves

[friral]

Newest Author is now Lightning fast although i upgraded to 55k books while waiting for the release:

2024-10-28 22:07:46.397 DEBUG Loaded filterdata in 41.66s

2024-10-28 22:07:46.426 DEBUG [ApiCacheManager] Cache miss: {"user":"xxx","url":"/libraries/xxx?include=filterdata"}

2024-10-28 22:07:47.007 DEBUG [ApiCacheManager] count: 1 size: 2063455

2024-10-28 22:07:47.601 DEBUG Loaded 10 of 11 items for "Continue Listening/Reading" in 0.59s

2024-10-28 22:07:53.075 DEBUG Loaded 0 of 0 items for "Continue Series" in 5.47s

2024-10-28 22:07:53.603 DEBUG Loaded 10 of 55197 items for "Recently Added" in 0.53s

2024-10-28 22:07:53.645 DEBUG Loaded 5 of 2466 series for "Recent Series" in 0.04s

2024-10-28 22:07:57.814 DEBUG Loaded 10 of 47369 items for "Discover" in 4.17s

2024-10-28 22:07:58.422 DEBUG Loaded 5 of 5 items for "Listen/Read Again" in 0.61s

2024-10-28 22:07:59.184 DEBUG Loaded 10 of 23396 authors for "Newest Authors" in 0.76s

2024-10-28 22:07:59.184 DEBUG Loaded 6 personalized shelves in 12.17s

There is still some sigificant load time for the filterdata which I did not notice before, not sure if this was that high before the update or if it went up now. On the other hand it does seem to be slow only the first time opening the homepage after I add some new books. Any consequential clicks on the homepage took only a few seconds overall to load. Not sure when exaclty it is slow and when it is quick, needs some further testing ond abservation i guess.
Overall the App becomes much more usable again for me so I appreciate your effort a lot so far!

[nichwall]

@friral Would you be willing to share your database for investigation for the long filterdata time? I've been experimenting with new indexes, but haven't found anything yet.