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 #3536] [MERGED] Migration indexes #4009

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

📋 Pull Request Information

Original PR: https://github.com/advplyr/audiobookshelf/pull/3536
Author: @nichwall
Created: 10/19/2024
Status: ✅ Merged
Merged: 10/19/2024
Merged by: @advplyr

Base: master ← Head: migration_indexes

📝 Commits (5)

  • 1fa80e3 Add: migrations for authors, series, and podcast episodes
  • ea6882d Update changelog
  • e8a1ea3 Fix: table naming
  • 84012d9 Fix: podcast episode index name
  • 35e2681 Update index creation migration to be idempotent

📊 Changes

5 files changed (+110 additions, -3 deletions)

View changed files

📝 server/migrations/changelog.md (+1 -0)
➕ server/migrations/v2.15.2-index-creation.js (+93 -0)
📝 server/models/BookAuthor.js (+7 -1)
📝 server/models/BookSeries.js (+7 -1)
📝 server/models/PodcastEpisode.js (+2 -1)

📄 Description

This PR fixes https://github.com/advplyr/audiobookshelf/issues/3259, https://github.com/advplyr/audiobookshelf/issues/3525, and https://github.com/advplyr/audiobookshelf/issues/3237.

This PR adds migrations for the following indices:

The author and series indexes reduce query time from multiple seconds/minutes for large databases (more than 20k items) to less than a second. I have not done much testing with large podcast databases yet. I am still investigating why some of the select book queries did not improve too much and whether this can be solved by another index.

To test the difference in query time, I did the following on a moderately sized database so the loop ran in a reasonable amount of time.
Database stats:

  • Authors: 3217
  • Series: 947
  • Books: 5892
  1. Enable benchmark logging for each SQL query
  2. Generated a HAR file of navigating around through the web client to get a variety of SQL requests
  3. Used a combination of Python/bash scripts to:
    • Delete and copy database from a backup to start at the same point for all tests
    • Start the server
    • Run all requests from HAR file
    • Stop the server
    • Repeat above steps 10 times
    • Copy the log file and rename according to index so we can keep all queries for this specific test separate
    • Parse the log files to build a table comparing worst time of each query for each data set

I sorted the times by the runtime without indexes, and created the following table (did not include all sets of indexes being added to show best/worst case):
Book_Index_Comparison

🔄 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/3536 **Author:** [@nichwall](https://github.com/nichwall) **Created:** 10/19/2024 **Status:** ✅ Merged **Merged:** 10/19/2024 **Merged by:** [@advplyr](https://github.com/advplyr) **Base:** `master` ← **Head:** `migration_indexes` --- ### 📝 Commits (5) - [`1fa80e3`](https://github.com/advplyr/audiobookshelf/commit/1fa80e31d1c855041939b49ce78e7c510f2affea) Add: migrations for authors, series, and podcast episodes - [`ea6882d`](https://github.com/advplyr/audiobookshelf/commit/ea6882d9aba1ee5dcb1f1de98dc9036ee962d19b) Update changelog - [`e8a1ea3`](https://github.com/advplyr/audiobookshelf/commit/e8a1ea3b541888f49c9809f40bb341f193dc085e) Fix: table naming - [`84012d9`](https://github.com/advplyr/audiobookshelf/commit/84012d9090221da442ef1d6cf9b271ef31db92a9) Fix: podcast episode index name - [`35e2681`](https://github.com/advplyr/audiobookshelf/commit/35e2681ea9d1193b259e5a73b3643dc876b8a48c) Update index creation migration to be idempotent ### 📊 Changes **5 files changed** (+110 additions, -3 deletions) <details> <summary>View changed files</summary> 📝 `server/migrations/changelog.md` (+1 -0) ➕ `server/migrations/v2.15.2-index-creation.js` (+93 -0) 📝 `server/models/BookAuthor.js` (+7 -1) 📝 `server/models/BookSeries.js` (+7 -1) 📝 `server/models/PodcastEpisode.js` (+2 -1) </details> ### 📄 Description This PR fixes https://github.com/advplyr/audiobookshelf/issues/3259, https://github.com/advplyr/audiobookshelf/issues/3525, and https://github.com/advplyr/audiobookshelf/issues/3237. This PR adds migrations for the following indices: - `BookAuthor` on `authorId` - `BookSeries` on `seriesId` - `PodcastEpisode` on `createdAt` and `podcastId` from https://github.com/advplyr/audiobookshelf/pull/3528 The author and series indexes reduce query time from multiple seconds/minutes for large databases (more than 20k items) to less than a second. I have not done much testing with large podcast databases yet. I am still investigating why some of the select book queries did not improve too much and whether this can be solved by another index. To test the difference in query time, I did the following on a moderately sized database so the loop ran in a reasonable amount of time. Database stats: - Authors: 3217 - Series: 947 - Books: 5892 1. Enable benchmark logging for each SQL query 2. Generated a HAR file of navigating around through the web client to get a variety of SQL requests 3. Used a combination of Python/bash scripts to: - Delete and copy database from a backup to start at the same point for all tests - Start the server - Run all requests from HAR file - Stop the server - Repeat above steps 10 times - Copy the log file and rename according to index so we can keep all queries for this specific test separate - Parse the log files to build a table comparing worst time of each query for each data set I sorted the times by the runtime without indexes, and created the following table (did not include all sets of indexes being added to show best/worst case): ![Book_Index_Comparison](https://github.com/user-attachments/assets/e4a44b5b-3a71-451e-91ea-642961e5c4f2) --- <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:17:57 +02:00
adam closed this issue 2026-04-25 00:17:57 +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#4009
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?