[Bug]: Doesn't load library

[racerand]

Checklist

• I am using an up-to-date version.
• I have read the documentation.
• I have searched existing issues.

TagStudio Version

Library 103 with commit 88d0b47

Operating System & Version

NixOS 25.05

Description

When loading my library with 146000+ entries it never appears to finish loading.
I can see that it tries to execute:

SELECT DISTINCT entries.id
FROM entries
WHERE NOT (EXISTS (SELECT 1 
    FROM tags, tag_entries 
    WHERE entries.id = tag_entries.entry_id AND tags.id = tag_entries.tag_id AND tags.is_hidden)) 
ORDER BY entries.id DESC

But the application becomes unresponsive and after leaving it for 20+ min nothing appears to have changed.
I executed the same SQL in sqlitebrowser and it also never finished.

I tried executing:

SELECT DISTINCT entries.id 
FROM entries 
WHERE NOT 
	(entries.id IN 
		(SELECT tag_entries.entry_id 
		FROM tags JOIN tag_entries 
		WHERE tags.is_hidden AND tags.id = tag_entries.tag_id)) 
ORDER BY entries.id DESC

Instead, and the response was instant, but I'm not sure how to translate this to alchemy code

Expected Behavior

Loading the library should at most take a few minutes.

Steps to Reproduce

1. Create large library
2. Open large library

Logs

2025-12-02 00:35:17 [info     ] [FFmpeg] Using FFprobe location: ffprobe (Found)
2025-12-02 00:35:17 [info     ] [FFmpeg] Using FFmpeg location: ffmpeg (Found)
2025-12-02 00:35:17 [info     ] [ResourceManager] Resources Registered: count=37
2025-12-02 00:35:17 [info     ] [Settings] Global Settings File Path not specified, using default
2025-12-02 00:35:17 [info     ] [Settings] Reading Global Settings File path=PosixPath('/home/racerand/.config/TagStudio/settings.toml')
2025-12-02 00:35:17 [info     ] [Cache] Cache File not specified, using default one filename=/home/racerand/.config/TagStudio/TagStudio.ini
qt.multimedia.ffmpeg: Using Qt multimedia with FFmpeg version 8.0 GPL version 3 or later
Failed to open VDPAU backend libvdpau_nvidia.so: cannot open shared object file: No such file or directory
2025-12-02 00:35:19 [error    ] Path does not exist.           open_path=None
2025-12-02 00:35:42 [info     ] [Library] Opening SQLite Library connection_string=sqlite:////run/media/racerand/4bd980f1-0194-4683-9164-d1aaa53047a4/Fansly/.TagStudio/ts_library.sqlite library_dir=PosixPath('/run/media/racerand/4bd980f1-0194-4683-9164-d1aaa53047a4/Fansly')
2025-12-02 00:35:42 [info     ] [Library] DB_VERSION: 103     
2025-12-02 00:35:42 [info     ] [Library] Creating DB tables...
2025-12-02 00:35:42 [debug    ] ValueType already exists       field=<FieldID.TITLE: DefaultField(id=0, name='Title', type=<FieldTypeEnum.TEXT_LINE: 'Text Line'>, is_default=True)>
2025-12-02 00:35:42 [debug    ] ValueType already exists       field=<FieldID.AUTHOR: DefaultField(id=1, name='Author', type=<FieldTypeEnum.TEXT_LINE: 'Text Line'>, is_default=False)>
2025-12-02 00:35:42 [debug    ] ValueType already exists       field=<FieldID.ARTIST: DefaultField(id=2, name='Artist', type=<FieldTypeEnum.TEXT_LINE: 'Text Line'>, is_default=False)>
2025-12-02 00:35:42 [debug    ] ValueType already exists       field=<FieldID.URL: DefaultField(id=3, name='URL', type=<FieldTypeEnum.TEXT_LINE: 'Text Line'>, is_default=False)>
2025-12-02 00:35:42 [debug    ] ValueType already exists       field=<FieldID.DESCRIPTION: DefaultField(id=4, name='Description', type=<FieldTypeEnum.TEXT_BOX: 'Text Box'>, is_default=False)>
2025-12-02 00:35:42 [debug    ] ValueType already exists       field=<FieldID.NOTES: DefaultField(id=5, name='Notes', type=<FieldTypeEnum.TEXT_BOX: 'Text Box'>, is_default=False)>
2025-12-02 00:35:42 [debug    ] ValueType already exists       field=<FieldID.COLLATION: DefaultField(id=9, name='Collation', type=<FieldTypeEnum.TEXT_LINE: 'Text Line'>, is_default=False)>
2025-12-02 00:35:42 [debug    ] ValueType already exists       field=<FieldID.DATE: DefaultField(id=10, name='Date', type=<FieldTypeEnum.DATETIME: 'Datetime'>, is_default=False)>
2025-12-02 00:35:42 [debug    ] ValueType already exists       field=<FieldID.DATE_CREATED: DefaultField(id=11, name='Date Created', type=<FieldTypeEnum.DATETIME: 'Datetime'>, is_default=False)>
2025-12-02 00:35:42 [debug    ] ValueType already exists       field=<FieldID.DATE_MODIFIED: DefaultField(id=12, name='Date Modified', type=<FieldTypeEnum.DATETIME: 'Datetime'>, is_default=False)>
2025-12-02 00:35:42 [debug    ] ValueType already exists       field=<FieldID.DATE_TAKEN: DefaultField(id=13, name='Date Taken', type=<FieldTypeEnum.DATETIME: 'Datetime'>, is_default=False)>
2025-12-02 00:35:42 [debug    ] ValueType already exists       field=<FieldID.DATE_PUBLISHED: DefaultField(id=14, name='Date Published', type=<FieldTypeEnum.DATETIME: 'Datetime'>, is_default=False)>
2025-12-02 00:35:42 [debug    ] ValueType already exists       field=<FieldID.BOOK: DefaultField(id=17, name='Book', type=<FieldTypeEnum.TEXT_LINE: 'Text Line'>, is_default=False)>
2025-12-02 00:35:42 [debug    ] ValueType already exists       field=<FieldID.COMIC: DefaultField(id=18, name='Comic', type=<FieldTypeEnum.TEXT_LINE: 'Text Line'>, is_default=False)>
2025-12-02 00:35:42 [debug    ] ValueType already exists       field=<FieldID.SERIES: DefaultField(id=19, name='Series', type=<FieldTypeEnum.TEXT_LINE: 'Text Line'>, is_default=False)>
2025-12-02 00:35:42 [debug    ] ValueType already exists       field=<FieldID.MANGA: DefaultField(id=20, name='Manga', type=<FieldTypeEnum.TEXT_LINE: 'Text Line'>, is_default=False)>
2025-12-02 00:35:42 [debug    ] ValueType already exists       field=<FieldID.SOURCE: DefaultField(id=21, name='Source', type=<FieldTypeEnum.TEXT_LINE: 'Text Line'>, is_default=False)>
2025-12-02 00:35:42 [debug    ] ValueType already exists       field=<FieldID.DATE_UPLOADED: DefaultField(id=22, name='Date Uploaded', type=<FieldTypeEnum.DATETIME: 'Datetime'>, is_default=False)>
2025-12-02 00:35:42 [debug    ] ValueType already exists       field=<FieldID.DATE_RELEASED: DefaultField(id=23, name='Date Released', type=<FieldTypeEnum.DATETIME: 'Datetime'>, is_default=False)>
2025-12-02 00:35:42 [debug    ] ValueType already exists       field=<FieldID.VOLUME: DefaultField(id=24, name='Volume', type=<FieldTypeEnum.TEXT_LINE: 'Text Line'>, is_default=False)>
2025-12-02 00:35:42 [debug    ] ValueType already exists       field=<FieldID.ANTHOLOGY: DefaultField(id=25, name='Anthology', type=<FieldTypeEnum.TEXT_LINE: 'Text Line'>, is_default=False)>
2025-12-02 00:35:42 [debug    ] ValueType already exists       field=<FieldID.MAGAZINE: DefaultField(id=26, name='Magazine', type=<FieldTypeEnum.TEXT_LINE: 'Text Line'>, is_default=False)>
2025-12-02 00:35:42 [debug    ] ValueType already exists       field=<FieldID.PUBLISHER: DefaultField(id=27, name='Publisher', type=<FieldTypeEnum.TEXT_LINE: 'Text Line'>, is_default=False)>
2025-12-02 00:35:42 [debug    ] ValueType already exists       field=<FieldID.GUEST_ARTIST: DefaultField(id=28, name='Guest Artist', type=<FieldTypeEnum.TEXT_LINE: 'Text Line'>, is_default=False)>
2025-12-02 00:35:42 [debug    ] ValueType already exists       field=<FieldID.COMPOSER: DefaultField(id=29, name='Composer', type=<FieldTypeEnum.TEXT_LINE: 'Text Line'>, is_default=False)>
2025-12-02 00:35:42 [debug    ] ValueType already exists       field=<FieldID.COMMENTS: DefaultField(id=30, name='Comments', type=<FieldTypeEnum.TEXT_LINE: 'Text Line'>, is_default=False)>
2025-12-02 00:35:43 [info     ] [Config] Thumbnail Cache Size: 1.02 GB
2025-12-02 00:35:43 [info     ] [Ignore] Processing the .ts_ignore file... last_mtime=None library=PosixPath('/run/media/racerand/4bd980f1-0194-4683-9164-d1aaa53047a4/Fansly') new_mtime=1763158157.844917
2025-12-02 00:35:43 [info     ] [Ignore]                       glob_patterns=[<Censored>]
2025-12-02 00:35:43 [warning  ] [PanelModal] add_callback not implemented for IgnoreModal
2025-12-02 00:35:43 [info     ] [Ignore] No updates to the .ts_ignore detected last_mtime=1763158157.844917 library=PosixPath('<PATH_TO_LIBRARY') new_mtime=1763158157.844917
2025-12-02 00:35:43 [info     ] searching library              filter=BrowsingState(page_index=0, sorting_mode=<SortingModeEnum.DATE_ADDED: 'file.date_added'>, ascending=False, random_seed=0, show_hidden_entries=False, query=None) query_full='SELECT DISTINCT entries.id \nFROM entries \nWHERE NOT (EXISTS (SELECT 1 \nFROM tags, tag_entries \nWHERE entries.id = tag_entries.entry_id AND tags.id = tag_entries.tag_id AND tags.is_hidden)) ORDER BY entries.id DESC'

[CyanVoxel]

Unfortunately I'm not able to reproduce this behavior - my test libraries including a large 100,000+ file library with thousands of tags with the is_hidden property are functioning normally and don't get hung up on the starting query..,

[CyanVoxel]

Is this issue only present on the latest commit? And does opening the affected library in a slightly older version (v9.5.4+) still produce the issue?

[racerand]

It worked on 9.5.6, only the latest commit affects it. Also, I have 1750+ tags, which might be more relevant than the entry count

[racerand]

Also just tested with c38cc9d without issues, so the latest commit is confirmed to cause the issue

[TrigamDev]

Unfortunately I'm not able to reproduce this behavior - my test libraries including a large 100,000+ file library with thousands of tags with the is_hidden property are functioning normally and don't get hung up on the starting query..,

Created a library with ~140k entries, and I was also unable to reproduce the bug. Though, the library only contained the default 'Favorite', 'Archive', and 'Meta Tags' tags, and no entries were tagged.

[CyanVoxel]

In the 100k+ library I used for testing I also have about 2,000 tags, and about 1,300 entries are already tagged with the "Archived" tag that includes the hidden property. I've also failed to reproduce the issue on large libraries with just the default tags and nothing tagged.

[TrigamDev]

After tagging ~6k entries with the 'Archived' tag, it does appears as though I was able to replicate the bug

[racerand]

I've now implemented a replacement request as suggested by BobBobs in Discord, that performs really well both without any and with thousands of hidden tags. This would be the new query:

hidden_tag_ids = select(Tag.id).where(Tag.is_hidden)
     hidden_entry_ids = select(TagEntry.entry_id).where(
    TagEntry.tag_id.in_(hidden_tag_ids)
)
statement = statement.where(Entry.id.not_in(hidden_entry_ids))

It could potentially be more efficient with Indices on TagEntry, but doesn't seem necessary.

[TrigamDev]

It could potentially be more efficient with Indices on TagEntry, but doesn't seem necessary.

This is implemented already by #1129, so if merged, this would be taken care of anyways.