[PR #387] [MERGED] sqlite: remove unneeded LEFT JOIN to improve performance #581

New issue

Closed

opened 2026-02-25 23:34:48 +03:00 by kerem · 0 comments

kerem commented

2026-02-25 23:34:48 +03:00

Owner

📋 Pull Request Information

Original PR: https://github.com/go-shiori/shiori/pull/387
Author: @tungel
Created: 2/26/2022
Status: ✅ Merged
Merged: 3/10/2022
Merged by: @fmartingr

Base: master ← Head: master

📝 Commits (3)

d1c6247 sqlite: remove unneeded LEFT JOIN to improve performance
01943fa Fix handling join clause in GetBookmarks
6af589d Revert changes in GetBookmarks

📊 Changes

1 file changed (+0 additions, -1 deletions)

View changed files

📝 internal/database/sqlite.go (+0 -1)

📄 Description

I came across this project a few days ago on HackerNews and I like it very much. After using it a bit more during the last few days, I notice that the performance degrades quickly. With 1187 bookmarks (40 pages), running the shiori server (using sqlite db) on MacBook Pro (16-inch, 2019) 2.6 GHz 6-Core Intel Core i7 took around 10 seconds to load and similar amount of time to switch between pages.

I managed to track down the culprit - it's the LEFT JOIN statements in the GetBookmarks and GetBookmarksCount methods in the sqlite.go that cause the slowness.

I'm not very familiar with the code base yet but it looks like we don't need LEFT JOIN in those methods. Removing it helps improving performance dramatically.

Before:

INFO[0000] Serve shiori in :8050 /
WARN[0005] GET /api/bookmarks?keyword=&tags=&exclude=&page=1  proto=HTTP/1.1 remote="127.0.0.1:54846" reqlen=0 size=25 status=500
INFO[0007] GET /login?dst=http%3A%2F%2Flocalhost%3A8050%2F%23home  proto=HTTP/1.1 remote="127.0.0.1:54846" reqlen=0 size=4376 status=200
INFO[0010] POST /api/login                               proto=HTTP/1.1 remote="127.0.0.1:54846" reqlen=61 size=101 status=200
INFO[0010] GET /                                         proto=HTTP/1.1 remote="127.0.0.1:54846" reqlen=0 size=5659 status=200
2022/02/26 10:43:21 Start GetBookmarksCount...
2022/02/26 10:43:32 End GetBookmarksCount
2022/02/26 10:43:32 Start GetBookmarks...
2022/02/26 10:43:32 End GetBookmarks
INFO[0021] GET /api/bookmarks?keyword=&tags=&exclude=&page=1  proto=HTTP/1.1 remote="127.0.0.1:54846" reqlen=0 size=10997 status=200
INFO[0021] GET /api/tags                                 proto=HTTP/1.1 remote="127.0.0.1:54846" reqlen=0 size=4206 status=200
2022/02/26 10:43:52 Start GetBookmarksCount...
2022/02/26 10:44:02 End GetBookmarksCount
2022/02/26 10:44:02 Start GetBookmarks...
2022/02/26 10:44:02 End GetBookmarks
INFO[0051] GET /api/bookmarks?keyword=&tags=&exclude=&page=1  proto=HTTP/1.1 remote="127.0.0.1:55174" reqlen=0 size=10997 status=200
INFO[0051] GET /api/tags                                 proto=HTTP/1.1 remote="127.0.0.1:55174" reqlen=0 size=4206 status=200
2022/02/26 10:44:18 Start GetBookmarksCount...
2022/02/26 10:44:29 End GetBookmarksCount
2022/02/26 10:44:29 Start GetBookmarks...
2022/02/26 10:44:30 End GetBookmarks
INFO[0079] GET /api/bookmarks?keyword=&tags=&exclude=&page=2  proto=HTTP/1.1 remote="127.0.0.1:55419" reqlen=0 size=9959 status=200
2022/02/26 10:44:42 Start GetBookmarksCount...
2022/02/26 10:44:52 End GetBookmarksCount
2022/02/26 10:44:52 Start GetBookmarks...
2022/02/26 10:44:53 End GetBookmarks
INFO[0102] GET /api/bookmarks?keyword=&tags=&exclude=&page=3  proto=HTTP/1.1 remote="127.0.0.1:55629" reqlen=0 size=10289 status=200

After

INFO[0000] Serve shiori in :8050 /
WARN[0008] GET /                                         proto=HTTP/1.1 remote="127.0.0.1:56649" reqlen=0 size=49 status=301
INFO[0008] GET /login?dst=%2F                            proto=HTTP/1.1 remote="127.0.0.1:56649" reqlen=0 size=4376 status=200
INFO[0008] GET /css/fonts/source-sans-pro-v13-latin-200.woff2  proto=HTTP/1.1 remote="127.0.0.1:56649" reqlen=0 size=15824 status=200
INFO[0017] POST /api/login                               proto=HTTP/1.1 remote="127.0.0.1:56733" reqlen=61 size=101 status=200
INFO[0017] GET /                                         proto=HTTP/1.1 remote="127.0.0.1:56733" reqlen=0 size=5659 status=200
2022/02/26 10:46:47 Start GetBookmarksCount...
2022/02/26 10:46:47 End GetBookmarksCount
2022/02/26 10:46:47 Start GetBookmarks...
2022/02/26 10:46:47 End GetBookmarks
INFO[0017] GET /api/bookmarks?keyword=&tags=&exclude=&page=1  proto=HTTP/1.1 remote="127.0.0.1:56733" reqlen=0 size=10997 status=200
INFO[0017] GET /api/tags                                 proto=HTTP/1.1 remote="127.0.0.1:56733" reqlen=0 size=4206 status=200
2022/02/26 10:46:52 Start GetBookmarksCount...
2022/02/26 10:46:52 End GetBookmarksCount
2022/02/26 10:46:52 Start GetBookmarks...
2022/02/26 10:46:52 End GetBookmarks
INFO[0023] GET /api/bookmarks?keyword=&tags=&exclude=&page=1  proto=HTTP/1.1 remote="127.0.0.1:56733" reqlen=0 size=10997 status=200
INFO[0023] GET /api/tags                                 proto=HTTP/1.1 remote="127.0.0.1:56733" reqlen=0 size=4206 status=200
2022/02/26 10:47:02 Start GetBookmarksCount...
2022/02/26 10:47:02 End GetBookmarksCount
2022/02/26 10:47:02 Start GetBookmarks...
2022/02/26 10:47:02 End GetBookmarks
INFO[0033] GET /api/bookmarks?keyword=&tags=&exclude=&page=2  proto=HTTP/1.1 remote="127.0.0.1:56733" reqlen=0 size=9959 status=200
2022/02/26 10:47:09 Start GetBookmarksCount...
2022/02/26 10:47:09 End GetBookmarksCount
2022/02/26 10:47:09 Start GetBookmarks...
2022/02/26 10:47:09 End GetBookmarks
INFO[0039] GET /api/bookmarks?keyword=&tags=&exclude=&page=3  proto=HTTP/1.1 remote="127.0.0.1:56733" reqlen=0 size=10289 status=200

_{🔄 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/go-shiori/shiori/pull/387 **Author:** [@tungel](https://github.com/tungel) **Created:** 2/26/2022 **Status:** ✅ Merged **Merged:** 3/10/2022 **Merged by:** [@fmartingr](https://github.com/fmartingr) **Base:** `master` ← **Head:** `master` --- ### 📝 Commits (3) - [`d1c6247`](https://github.com/go-shiori/shiori/commit/d1c62475be42d3c18273c0c7fc4a7d1b485cd517) sqlite: remove unneeded LEFT JOIN to improve performance - [`01943fa`](https://github.com/go-shiori/shiori/commit/01943fafb07fabd3fc424c92cdf7b02c985a579b) Fix handling join clause in GetBookmarks - [`6af589d`](https://github.com/go-shiori/shiori/commit/6af589dcf77d20273f179bd5bed6a9f43a6412d9) Revert changes in GetBookmarks ### 📊 Changes **1 file changed** (+0 additions, -1 deletions) <details> <summary>View changed files</summary> 📝 `internal/database/sqlite.go` (+0 -1) </details> ### 📄 Description I came across this project a few days ago on HackerNews and I like it very much. After using it a bit more during the last few days, I notice that the performance degrades quickly. With 1187 bookmarks (40 pages), running the shiori server (using sqlite db) on MacBook Pro (16-inch, 2019) 2.6 GHz 6-Core Intel Core i7 took around 10 seconds to load and similar amount of time to switch between pages. I managed to track down the culprit - it's the `LEFT JOIN` statements in the `GetBookmarks` and `GetBookmarksCount` methods in the `sqlite.go` that cause the slowness. I'm not very familiar with the code base yet but it looks like we don't need `LEFT JOIN` in those methods. Removing it helps improving performance dramatically. Before: ``` INFO[0000] Serve shiori in :8050 / WARN[0005] GET /api/bookmarks?keyword=&tags=&exclude=&page=1 proto=HTTP/1.1 remote="127.0.0.1:54846" reqlen=0 size=25 status=500 INFO[0007] GET /login?dst=http%3A%2F%2Flocalhost%3A8050%2F%23home proto=HTTP/1.1 remote="127.0.0.1:54846" reqlen=0 size=4376 status=200 INFO[0010] POST /api/login proto=HTTP/1.1 remote="127.0.0.1:54846" reqlen=61 size=101 status=200 INFO[0010] GET / proto=HTTP/1.1 remote="127.0.0.1:54846" reqlen=0 size=5659 status=200 2022/02/26 10:43:21 Start GetBookmarksCount... 2022/02/26 10:43:32 End GetBookmarksCount 2022/02/26 10:43:32 Start GetBookmarks... 2022/02/26 10:43:32 End GetBookmarks INFO[0021] GET /api/bookmarks?keyword=&tags=&exclude=&page=1 proto=HTTP/1.1 remote="127.0.0.1:54846" reqlen=0 size=10997 status=200 INFO[0021] GET /api/tags proto=HTTP/1.1 remote="127.0.0.1:54846" reqlen=0 size=4206 status=200 2022/02/26 10:43:52 Start GetBookmarksCount... 2022/02/26 10:44:02 End GetBookmarksCount 2022/02/26 10:44:02 Start GetBookmarks... 2022/02/26 10:44:02 End GetBookmarks INFO[0051] GET /api/bookmarks?keyword=&tags=&exclude=&page=1 proto=HTTP/1.1 remote="127.0.0.1:55174" reqlen=0 size=10997 status=200 INFO[0051] GET /api/tags proto=HTTP/1.1 remote="127.0.0.1:55174" reqlen=0 size=4206 status=200 2022/02/26 10:44:18 Start GetBookmarksCount... 2022/02/26 10:44:29 End GetBookmarksCount 2022/02/26 10:44:29 Start GetBookmarks... 2022/02/26 10:44:30 End GetBookmarks INFO[0079] GET /api/bookmarks?keyword=&tags=&exclude=&page=2 proto=HTTP/1.1 remote="127.0.0.1:55419" reqlen=0 size=9959 status=200 2022/02/26 10:44:42 Start GetBookmarksCount... 2022/02/26 10:44:52 End GetBookmarksCount 2022/02/26 10:44:52 Start GetBookmarks... 2022/02/26 10:44:53 End GetBookmarks INFO[0102] GET /api/bookmarks?keyword=&tags=&exclude=&page=3 proto=HTTP/1.1 remote="127.0.0.1:55629" reqlen=0 size=10289 status=200 ``` After ``` INFO[0000] Serve shiori in :8050 / WARN[0008] GET / proto=HTTP/1.1 remote="127.0.0.1:56649" reqlen=0 size=49 status=301 INFO[0008] GET /login?dst=%2F proto=HTTP/1.1 remote="127.0.0.1:56649" reqlen=0 size=4376 status=200 INFO[0008] GET /css/fonts/source-sans-pro-v13-latin-200.woff2 proto=HTTP/1.1 remote="127.0.0.1:56649" reqlen=0 size=15824 status=200 INFO[0017] POST /api/login proto=HTTP/1.1 remote="127.0.0.1:56733" reqlen=61 size=101 status=200 INFO[0017] GET / proto=HTTP/1.1 remote="127.0.0.1:56733" reqlen=0 size=5659 status=200 2022/02/26 10:46:47 Start GetBookmarksCount... 2022/02/26 10:46:47 End GetBookmarksCount 2022/02/26 10:46:47 Start GetBookmarks... 2022/02/26 10:46:47 End GetBookmarks INFO[0017] GET /api/bookmarks?keyword=&tags=&exclude=&page=1 proto=HTTP/1.1 remote="127.0.0.1:56733" reqlen=0 size=10997 status=200 INFO[0017] GET /api/tags proto=HTTP/1.1 remote="127.0.0.1:56733" reqlen=0 size=4206 status=200 2022/02/26 10:46:52 Start GetBookmarksCount... 2022/02/26 10:46:52 End GetBookmarksCount 2022/02/26 10:46:52 Start GetBookmarks... 2022/02/26 10:46:52 End GetBookmarks INFO[0023] GET /api/bookmarks?keyword=&tags=&exclude=&page=1 proto=HTTP/1.1 remote="127.0.0.1:56733" reqlen=0 size=10997 status=200 INFO[0023] GET /api/tags proto=HTTP/1.1 remote="127.0.0.1:56733" reqlen=0 size=4206 status=200 2022/02/26 10:47:02 Start GetBookmarksCount... 2022/02/26 10:47:02 End GetBookmarksCount 2022/02/26 10:47:02 Start GetBookmarks... 2022/02/26 10:47:02 End GetBookmarks INFO[0033] GET /api/bookmarks?keyword=&tags=&exclude=&page=2 proto=HTTP/1.1 remote="127.0.0.1:56733" reqlen=0 size=9959 status=200 2022/02/26 10:47:09 Start GetBookmarksCount... 2022/02/26 10:47:09 End GetBookmarksCount 2022/02/26 10:47:09 Start GetBookmarks... 2022/02/26 10:47:09 End GetBookmarks INFO[0039] GET /api/bookmarks?keyword=&tags=&exclude=&page=3 proto=HTTP/1.1 remote="127.0.0.1:56733" reqlen=0 size=10289 status=200 ``` --- <sub>🔄 This issue represents a GitHub Pull Request. It cannot be merged through Gitea due to API limitations.</sub>