Possible performance degradation caused by SQLite automatic index on LastWatchedEpisodes

[Noryn 0]

**Issue Summary:**

Every evening, direct play sessions on my Emby server experience noticeable slowdowns around 22:10 to 22:30. Files that usually stream instantly in Direct Play (no transcoding) take 15 to 30 seconds to start and show signs of buffering. 

After observing the logs and testing different scenarios, I suspect that the slowdown may be related to an automatic SQLite index creation on what appears to be an internal structure: `LastWatchedEpisodes(SeriesPresentationUniqueKey)`.

---

**Environment:**

- Emby Server version: 4.8.11.0
- Platform: Debian 12 (bookworm)
- Architecture: x86_64 (Hetzner dedicated server)
- Emby installed natively (not Docker)
- Reverse proxy: NGINX (HTTPS)
- Storage: local (not SMB or NFS)
- Database: default (SQLite, library.db)

---

**Observations:**

Around 22:10 each evening, when Emby is accessed (via mobile or web), the following line appears repeatedly in the server logs: Sqlite: 284 - automatic index on LastWatchedEpisodes(SeriesPresentationUniqueKey)

 

At the same time, all `GET /Videos/.../original.mkv` requests take significantly longer to complete — 15 to 30 seconds — despite the fact that playback is occurring in full Direct Play with no transcoding.

While I can't confirm with certainty that the log entry and playback delay are causally linked, the correlation has been consistent over several days of testing. My understanding is that this index is being created dynamically when querying the "Continue Watching" or similar sections.

---

**What makes this challenging:**

- The table `LastWatchedEpisodes` does not appear to exist in the `library.db` file. I assume it may be a temporary view or internally generated structure.
- Because the index is recreated frequently (perhaps per session), SQLite performs this operation during user activity, introducing latency.
- This behavior is not documented, nor configurable from the Emby interface (as far as I know).

---

**Workaround (tested):**

To reduce the impact, I created a workaround:
- At 21:50 each evening, a script performs an authenticated API call to `/Users/{UserId}/Items?filters=IsResumable`.
- This appears to trigger the same query that causes the index creation.
- Doing this at a low-traffic time means users experience smooth playback afterwards.

This seems to mitigate the issue, but obviously isn't ideal long-term.

---

**Feature Request / Suggestion:**

- If applicable, allow Emby to create a persistent index for this data to avoid regeneration.
- Alternatively, provide a setting to delay or defer automatic indexing during active hours.
- And/or, document this behavior so sysadmins can plan accordingly.

---

Thanks for the great software, and for looking into this!
 

embyserver-63879099371 (1).txt

[Abobader 3464]

Hello Noryn,

** This is an auto reply **

Please wait for someone from staff support or our members to reply to you.

It's recommended to provide more info, as it explain in this thread:

Thank you.

Emby Team

[Happy2Play 9780]

What is the size of your library.db?

Not sure if it would make a difference but can you increase the database cache as it looks like yours is set to 300Mbs.

[Noryn 0]

15 minutes ago, Happy2Play said:

What is the size of your library.db?

Not sure if it would make a difference but can you increase the database cache as it looks like yours is set to 300Mbs.

Thanks a lot for your suggestion!

My library.db is currently around 37 MB, and I went ahead and applied your recommendation by setting to 800MB in the database config.

I’ll keep checking over the next few evenings to confirm that this fully mitigates the issue with the automatic index on LastWatchedEpisodes.

I'm still open to other ideas in case anyone has additional insights to share. Thanks again for the help!

Edited April 1, 2025 by Noryn

[Luke 42078]

HI, this should be improved a little bit in the upcoming 4.9 release.

[Happy2Play 9780]

Yes with a db that small the default value should be fine.  I was just guessing as most users library.db are at least 10x that size.   

As your previous setting was already 300 so technically if you made any changes it should have not affected anything.

2025-04-01 10:02:45.615 Info SqliteItemRepository: Opening sqlite connection to /var/lib/emby/data/library.db
2025-04-01 10:02:45.618 Info SqliteItemRepository: Default journal_mode for /var/lib/emby/data/library.db is wal
2025-04-01 10:02:45.618 Info SqliteItemRepository: PRAGMA cache_size=-307200

Dev may have to comment further.

[Noryn 0]

8 minutes ago, Luke said:

HI, this should be improved a little bit in the upcoming 4.9 release.

 

Thanks for confirming! I’ll keep monitoring until 4.9 lands and will gladly retest once it’s out. Really appreciate the follow-up.

 

7 minutes ago, Happy2Play said:

Yes with a db that small the default value should be fine.  I was just guessing as most users library.db are at least 10x that size.   

As your previous setting was already 300 so technically if you made any changes it should have not affected anything.

2025-04-01 10:02:45.615 Info SqliteItemRepository: Opening sqlite connection to /var/lib/emby/data/library.db
2025-04-01 10:02:45.618 Info SqliteItemRepository: Default journal_mode for /var/lib/emby/data/library.db is wal
2025-04-01 10:02:45.618 Info SqliteItemRepository: PRAGMA cache_size=-307200

Dev may have to comment further.

 

Thanks for the extra context — makes perfect sense!

Indeed, I noticed the `PRAGMA cache_size=-307200` line in the logs and I guess I underestimated that Emby already applies it by default. My updated value via the UI is now 800 MB, just to give SQLite a bit more headroom — but as you said, it probably wasn't a bottleneck at 300 anyway.

Thanks again for your input!
 

[Happy2Play 9780]

1 minute ago, Noryn said:

Thanks for the extra context — makes perfect sense!

Indeed, I noticed the `PRAGMA cache_size=-307200` line in the logs and I guess I underestimated that Emby already applies it by default. My updated value via the UI is now 800 MB, just to give SQLite a bit more headroom — but as you said, it probably wasn't a bottleneck at 300 anyway.

Thanks again for your input!

Try as it is recommended 1.5-2x db size.  Haven't tested a new install in a while so didn't think default values have changed from 96.