starred/audiobookshelf
1
0
Fork 0
mirror of https://github.com/advplyr/audiobookshelf.git synced 2026-05-30 23:40:40 +02:00
Code Issues 1.0k Packages Projects Releases 10 Wiki Activity

[PR #4080] [MERGED] Improve book library page query performance for author sort order #4162

New Issue
Closed
opened 2026-04-25 00:18:36 +02:00 by adam · 0 comments
adam commented 2026-04-25 00:18:36 +02:00
Owner
Copy Link
Copy Source

📋 Pull Request Information

Original PR: https://github.com/advplyr/audiobookshelf/pull/4080
Author: @mikiher
Created: 3/7/2025
Status: ✅ Merged
Merged: 3/17/2025
Merged by: @advplyr

Base: master ← Head: improve-author-sort-queries

📝 Commits (10+)

  • e0e1d2d Add migration to create authorNames* columns, in libraryItems including update triggers and indices
  • edccb72 Add authorNames columns and indices to LibraryItem model
  • b955b86 Add database triggers for updating author names in libraryItems (for new databases)
  • e4a7411 Populate authorNames during book scanning
  • 7f7ae97 Update book sorting to use new authorNames columns
  • 82af8d1 Add an index on podcastEpisodes.publishedAt
  • 184e842 Update migration to v2.20.0
  • 250be3a Fix group_concat order by and update to sqlite 3.44.2
  • 4b3c0d6 Revert version
  • d3de1e5 Fix test and update triggers for new db

📊 Changes

11 files changed (+1133 additions, -151 deletions)

View changed files

📝 package-lock.json (+402 -134)
📝 package.json (+1 -1)
📝 server/Database.js (+69 -0)
📝 server/migrations/changelog.md (+1 -0)
➕ server/migrations/v2.20.0-improve-author-sort-queries.js (+272 -0)
📝 server/models/LibraryItem.js (+13 -1)
📝 server/models/PodcastEpisode.js (+4 -0)
📝 server/scanner/BookScanner.js (+2 -0)
📝 server/utils/queries/libraryItemsBookFilters.js (+2 -13)
📝 server/utils/queries/libraryItemsPodcastFilters.js (+6 -2)
➕ test/server/migrations/v2.20.0-improve-author-sort-queries.test.js (+361 -0)

📄 Description

Brief summary

Similar to #3952, this significantly improves book library page query performance for the following sort orders:

  • Author (First Last)
  • Author (Last, First)

Which issue is fixed?

No Issue. This is a continuation of my work on #2073

In-depth Description

To replace per-record subqueries, I added two denormalized columns to libraryItems, authorNamesFirstLast, and authorNames LastFirst. These columns are kept indexed and are updated when the corresponding records in bookAuthors/authors are modified.

Using the new columns in library page queries (instead of per-record subqueries and unindexed sorting) resulted in an optimized query plan, and significantly improved query performance.

How have you tested this?

Correctness

  • Throughly unit-tested the db migration with in memory Sequelize
  • Ran the migration on an existing large database
  • Tested on a newly created database (no migration)
    • new columns, indexes, and triggers are properly created
  • Tested population of new columns in newly scanned library items
  • Made sure new columns were correctly updated on bookAuthors/authors changes
    • Added and removed authors from books (existing and new authors)
    • Modifed author name
    • Removed author

Performance

Tested on loading 72 consecutive page of 35 books each, on first-last author sort order, on an ABS docker container running on a Synology 920+ NAS.

Last-first author sort order results were similar.

All measurements are in ms.

Summary

Overall, we see a ~97% drop in mean and median query time.

Current (edge):

[2025-03-07 06:38:14.983] INFO: [findAndCountAll] histogram values: [
  3152,  826,  816,  990,  907,  997, 1235, 1079,
  1058, 1576, 1158, 1201, 1342, 1197, 1392, 1293,
  1546, 1523, 1593, 1636, 1465, 1967, 1439, 1832,
  1772, 1731, 1819, 1805, 1713, 1777, 1862, 1858,
  1614, 2066, 1703, 1677, 1705, 1624, 1748, 1768,
  1895, 2022, 2259, 1696, 1990, 1759, 1856, 2074,
  2077, 2001, 2617, 2459, 1987, 2047, 2172, 2431,
  2762, 2241, 1983, 2371, 2101, 2144, 2201, 2382,
  2367, 2138, 2256, 2164, 2422, 2428, 2222, 2297
]
[2025-03-07 06:38:14.984] INFO: [findAndCountAll] histogram: Histogram {
  min: 816,
  max: 3153,
  mean: 1823.5555555555557,
  exceeds: 0,
  stddev: 473.1667514643838,
  count: 72,
  percentiles: SafeMap(9) [Map] {
    0 => 816,
    50 => 1819,
    75 => 2144,
    87.5 => 2366,
    93.75 => 2430,
    96.875 => 2616,
    98.4375 => 2762,
    99.21875 => 3152,
    100 => 3152
  }
}

After denormalizing authorNames:

[2025-03-07 07:03:36.957] INFO: [findAndCountAll] histogram values: [
  86, 51, 81, 94, 78, 50, 55, 69, 65, 50, 43, 59,
  65, 43, 52, 53, 65, 45, 56, 46, 61, 53, 59, 52,
  53, 84, 43, 57, 52, 52, 50, 54, 56, 47, 64, 53,
  58, 72, 54, 48, 59, 60, 76, 45, 51, 71, 56, 52,
  64, 90, 47, 51, 56, 65, 61, 50, 50, 58, 64, 80,
  71, 58, 75, 52, 55, 66, 53, 62, 79, 67, 55, 66,
  66
]
[2025-03-07 07:03:36.958] INFO: [findAndCountAll] histogram: Histogram {
  min: 43,
  max: 94,
  mean: 59.71232876712329,
  exceeds: 0,
  stddev: 11.520653819981632,
  count: 73,
  percentiles: SafeMap(9) [Map] {
    0 => 43,
    50 => 56,
    75 => 65,
    87.5 => 75,
    93.75 => 81,
    96.875 => 86,
    98.4375 => 90,
    99.21875 => 94,
    100 => 94
  }
}

🔄 This issue represents a GitHub Pull Request. It cannot be merged through Gitea due to API limitations.

## 📋 Pull Request Information **Original PR:** https://github.com/advplyr/audiobookshelf/pull/4080 **Author:** [@mikiher](https://github.com/mikiher) **Created:** 3/7/2025 **Status:** ✅ Merged **Merged:** 3/17/2025 **Merged by:** [@advplyr](https://github.com/advplyr) **Base:** `master` ← **Head:** `improve-author-sort-queries` --- ### 📝 Commits (10+) - [`e0e1d2d`](https://github.com/advplyr/audiobookshelf/commit/e0e1d2d380dd8a34a61ced0a6fa28095d8711c96) Add migration to create authorNames* columns, in libraryItems including update triggers and indices - [`edccb72`](https://github.com/advplyr/audiobookshelf/commit/edccb7241b721102e17333e35265c54c02581783) Add authorNames columns and indices to LibraryItem model - [`b955b86`](https://github.com/advplyr/audiobookshelf/commit/b955b869726e13d26e92e008dfc8f7befcae0421) Add database triggers for updating author names in libraryItems (for new databases) - [`e4a7411`](https://github.com/advplyr/audiobookshelf/commit/e4a7411ec1beff9a99513a4f2467fb11450005af) Populate authorNames during book scanning - [`7f7ae97`](https://github.com/advplyr/audiobookshelf/commit/7f7ae97070de55a132e9ef5dea241a446d171222) Update book sorting to use new authorNames columns - [`82af8d1`](https://github.com/advplyr/audiobookshelf/commit/82af8d19f2cc0639647b75c7e99e104c77e29714) Add an index on podcastEpisodes.publishedAt - [`184e842`](https://github.com/advplyr/audiobookshelf/commit/184e842580fa604a05baea36b7a81e266648a28e) Update migration to v2.20.0 - [`250be3a`](https://github.com/advplyr/audiobookshelf/commit/250be3a99a356b77aab192900f722d8daa57831f) Fix group_concat order by and update to sqlite 3.44.2 - [`4b3c0d6`](https://github.com/advplyr/audiobookshelf/commit/4b3c0d65dc40447b0cddcec4a046488fef292469) Revert version - [`d3de1e5`](https://github.com/advplyr/audiobookshelf/commit/d3de1e57ec8f070a2c3843566916c7ffad3c2906) Fix test and update triggers for new db ### 📊 Changes **11 files changed** (+1133 additions, -151 deletions) <details> <summary>View changed files</summary> 📝 `package-lock.json` (+402 -134) 📝 `package.json` (+1 -1) 📝 `server/Database.js` (+69 -0) 📝 `server/migrations/changelog.md` (+1 -0) ➕ `server/migrations/v2.20.0-improve-author-sort-queries.js` (+272 -0) 📝 `server/models/LibraryItem.js` (+13 -1) 📝 `server/models/PodcastEpisode.js` (+4 -0) 📝 `server/scanner/BookScanner.js` (+2 -0) 📝 `server/utils/queries/libraryItemsBookFilters.js` (+2 -13) 📝 `server/utils/queries/libraryItemsPodcastFilters.js` (+6 -2) ➕ `test/server/migrations/v2.20.0-improve-author-sort-queries.test.js` (+361 -0) </details> ### 📄 Description ## Brief summary Similar to #3952, this significantly improves book library page query performance for the following sort orders: - Author (First Last) - Author (Last, First) ## Which issue is fixed? No Issue. This is a continuation of my work on #2073 ## In-depth Description To replace per-record subqueries, I added two denormalized columns to `libraryItems`, `authorNamesFirstLast`, and `authorNames LastFirst`. These columns are kept indexed and are updated when the corresponding records in `bookAuthors`/`authors` are modified. Using the new columns in library page queries (instead of per-record subqueries and unindexed sorting) resulted in an optimized query plan, and significantly improved query performance. ## How have you tested this? ### Correctness - Throughly unit-tested the db migration with in memory Sequelize - Ran the migration on an existing large database - Tested on a newly created database (no migration) - new columns, indexes, and triggers are properly created - Tested population of new columns in newly scanned library items - Made sure new columns were correctly updated on bookAuthors/authors changes - Added and removed authors from books (existing and new authors) - Modifed author name - Removed author ### Performance Tested on loading 72 consecutive page of 35 books each, on first-last author sort order, on an ABS docker container running on a Synology 920+ NAS. Last-first author sort order results were similar. All measurements are in ms. #### **Summary** Overall, we see a **~97% drop** in mean and median query time. #### **Current (edge):** ``` [2025-03-07 06:38:14.983] INFO: [findAndCountAll] histogram values: [ 3152, 826, 816, 990, 907, 997, 1235, 1079, 1058, 1576, 1158, 1201, 1342, 1197, 1392, 1293, 1546, 1523, 1593, 1636, 1465, 1967, 1439, 1832, 1772, 1731, 1819, 1805, 1713, 1777, 1862, 1858, 1614, 2066, 1703, 1677, 1705, 1624, 1748, 1768, 1895, 2022, 2259, 1696, 1990, 1759, 1856, 2074, 2077, 2001, 2617, 2459, 1987, 2047, 2172, 2431, 2762, 2241, 1983, 2371, 2101, 2144, 2201, 2382, 2367, 2138, 2256, 2164, 2422, 2428, 2222, 2297 ] [2025-03-07 06:38:14.984] INFO: [findAndCountAll] histogram: Histogram { min: 816, max: 3153, mean: 1823.5555555555557, exceeds: 0, stddev: 473.1667514643838, count: 72, percentiles: SafeMap(9) [Map] { 0 => 816, 50 => 1819, 75 => 2144, 87.5 => 2366, 93.75 => 2430, 96.875 => 2616, 98.4375 => 2762, 99.21875 => 3152, 100 => 3152 } } ``` #### **After denormalizing authorNames:** ``` [2025-03-07 07:03:36.957] INFO: [findAndCountAll] histogram values: [ 86, 51, 81, 94, 78, 50, 55, 69, 65, 50, 43, 59, 65, 43, 52, 53, 65, 45, 56, 46, 61, 53, 59, 52, 53, 84, 43, 57, 52, 52, 50, 54, 56, 47, 64, 53, 58, 72, 54, 48, 59, 60, 76, 45, 51, 71, 56, 52, 64, 90, 47, 51, 56, 65, 61, 50, 50, 58, 64, 80, 71, 58, 75, 52, 55, 66, 53, 62, 79, 67, 55, 66, 66 ] [2025-03-07 07:03:36.958] INFO: [findAndCountAll] histogram: Histogram { min: 43, max: 94, mean: 59.71232876712329, exceeds: 0, stddev: 11.520653819981632, count: 73, percentiles: SafeMap(9) [Map] { 0 => 43, 50 => 56, 75 => 65, 87.5 => 75, 93.75 => 81, 96.875 => 86, 98.4375 => 90, 99.21875 => 94, 100 => 94 } } ``` --- <sub>🔄 This issue represents a GitHub Pull Request. It cannot be merged through Gitea due to API limitations.</sub>
adam added the pull-request label 2026-04-25 00:18:36 +02:00
adam closed this issue 2026-04-25 00:18:36 +02:00
Sign in to join this conversation.
No Branch/Tag Specified
Branches Tags
Labels
Clear labels
authentication
backlog
bug
chapter editor
config-issue
ebooks
encoding/embedding
enhancement
help wanted
listening sessions & progress
planned
possible plugin
progress sync
pull-request
Mirrored from GitHub Pull Request
 sorting/filtering/searching
unable to reproduce
upload
users & permissions
waiting
No Label pull-request
Milestone
No items
No Milestone
Projects
Clear projects
No project
Assignees
Clear assignees
adam (Adam Melkus)
No Assignees
1 Participants
Notifications
Due Date
No due date set.
Dependencies

No dependencies set.

Reference: starred/audiobookshelf#4162
Delete Branch "%!s()"

Deleting a branch is permanent. Although the deleted branch may continue to exist for a short time before it actually gets removed, it CANNOT be undone in most cases. Continue?