How the Hydrus Tag Search System Optimizes Queries with Caching

Hydrus accelerates tag searches by maintaining four families of specialized SQLite caches per service, pre-computing normalized sub-tags and wildcard expansions to eliminate expensive table scans.

The hydrusnetwork/hydrus media organizer uses a sophisticated external caching strategy to solve the performance bottleneck of wildcard tag searches. Instead of querying massive master tables for every autocomplete request, the system builds service-specific cache tables that store pre-normalized tag data, enabling sub-linear lookup times even with millions of indexed files.

The Four Cache Families in external_caches

Hydrus creates four distinct cache types for every combination of tag service (e.g., "All Tags") and file service (local, archive, deleted). These tables live in the external_caches SQLite schema and are managed by hydrus/client/db/ClientDBTagSearch.py.

Tag-to-ID Mapping

The tag-to-ID cache stores the relationship between tag_id and its component namespace_id and subtag_id. Table names follow the pattern combined_files_tags_cache_<tag_service_id> for combined searches or specific_tags_cache_<file_service_id>_<tag_service_id> for service-specific queries. This allows the engine to resolve tag identities without joining the global tags table.

Full-Text Search Index (FTS4)

To handle wildcard text efficiently, Hydrus maintains FTS4 virtual tables named combined_files_subtags_fts4_cache_<tag_service_id> or specific_subtags_fts4_cache_<file_service_id>_<tag_service_id>. These indices use SQLite's full-text search engine to convert prefix wildcards like sam* into optimized MATCH queries rather than slow LIKE scans.

Searchable Sub-Tag Normalization

The searchable-sub-tag map bridges raw user input to normalized search text. Stored in tables like combined_files_subtags_searchable_map_cache_<tag_service_id>, these maps cache the output of ClientSearchTagContext.ConvertSubtagToSearchable()—which lower-cases text and removes punctuation—ensuring normalization happens once per tag rather than per query.

Integer Sub-Tag Comparisons

For numeric tags (ratings, years, counts), the integer sub-tag cache (combined_files_integer_subtags_cache_<tag_service_id>) stores 64-bit integer values. This enables fast range comparisons (e.g., rating>=3) using standard SQLite integer indices instead of string coercion.

How Caches Are Built and Maintained

On-Demand Table Generation

When a tag service is first accessed, ClientDBTagSearch dynamically generates the required tables via _GetServiceTableGenerationDict. The class tracks services needing rebuilds through the self._missing_tag_search_service_pairs set defined in __init__(). Table creation occurs in the external_caches schema using formatted names:

subtags_fts4_table_name = f'external_caches.combined_files_subtags_fts4_cache_{suffix}'

Incremental Population via AddTags

As files are tagged, the AddTags() method (lines 95-108 of ClientDBTagSearch.py) synchronously updates all four cache families. The method inserts tag mappings and FTS4 entries using INSERT OR IGNORE to maintain idempotency:

# Update tag-to-ID cache

self._Execute(
    'INSERT OR IGNORE INTO {} ( tag_id, namespace_id, subtag_id ) '
    'SELECT tag_id, namespace_id, subtag_id FROM tags WHERE tag_id = ?;'
    .format(tags_table_name), (tag_id,))

# Update FTS4 searchable text

self._Execute(
    'INSERT OR IGNORE INTO {} ( docid, subtag ) VALUES ( ?, ? );'
    .format(subtags_fts4_table_name), 
    (subtag_id, searchable_subtag))

Numeric sub-tags are routed to the integer cache only if CanCacheInteger() (lines 35-38) confirms they fit within SQLite's 64-bit signed integer range.

Cache Regeneration from the UI

Users can force a complete rebuild through the GUI menu "Tag text search cache" (implemented in hydrus/client/gui/ClientGUI.py, lines 3406-3408). This operation truncates the cache tables and triggers repopulation via the standard AddTags logic, useful after bulk tag modifications or sibling/parent relationship changes.

Query Optimization Paths

Autocomplete and Wildcard Resolution

When the autocomplete pipeline receives input, it calls GetAutocompletePredicates(), which iterates over each leaf in the file-service search tree. For each leaf, GetAutocompleteTagIds() delegates wildcard resolution to GetSubtagIdsFromWildcard() (lines 29-55).

FTS4 vs. LIKE Fallback Strategy

GetSubtagIdsFromWildcard() implements a tiered lookup strategy based on wildcard complexity:

• Simple * wildcard: Performs a whole-table scan of the FTS4 index (SELECT docid FROM ...)
• Prefix wildcards (sam*): Uses FTS4 MATCH with LIKE prefix filtering (lines 84-90), achieving O(log N) performance
• Exact matches (samus): Issues quoted FTS4 match queries ("samus") for direct document ID retrieval (lines 94-100)
• Complex wildcards (*a* or a?): Falls back to LIKE queries against the raw sub-tag table when searchable normalization cannot be applied (lines 63-78)

Avoiding the Master Tags Table

For tag ID resolution, GetQueryPhraseForTagIds() (lines 22-26) queries the pre-combined cache tables rather than the master tags table. Because these service-specific caches contain only relevant tag IDs—typically a few kilobytes per service—the engine avoids joining against the multi-megabyte global tag registry.

Performance Characteristics

The cache architecture delivers sub-linear query costs through several mechanisms:

• Indexed lookups: All cache tables maintain SQLite indices via _GetServiceIndexGenerationDictSingle() (lines 76-88), ensuring O(log N) retrieval for tag IDs and sub-tag values
• FTS4 prefix optimization: Prefix wildcards leverage inverted indices rather than sequential scans, reducing millions of row examinations to logarithmic tree traversals
• Domain separation: Separate tables per file service (local, archive, deleted) constrain search spaces to relevant data subsets, eliminating cross-service noise
• Pre-computed normalization: The searchable-sub-tag map eliminates redundant string processing, while the integer cache avoids runtime string-to-number conversions

Summary

• Hydrus maintains four cache families per service in the external_caches schema: tag-to-ID mappings, FTS4 text indices, searchable sub-tag maps, and integer sub-tag stores
• Caches are populated incrementally via AddTags() in ClientDBTagSearch.py and can be regenerated through the GUI menu
• Wildcard queries use FTS4 MATCH for prefixes and exact matches, falling back to LIKE only for complex patterns
• Query resolution targets service-specific cache tables rather than the global tags table, achieving O(log N) performance
• Integer sub-tags receive specialized handling for fast numeric range comparisons

Frequently Asked Questions

How does Hydrus handle wildcard searches that start with an asterisk?

Complex wildcards like *character or m?n bypass the FTS4 index because SQLite FTS4 cannot efficiently search suffixes or single-character masks. In these cases, GetSubtagIdsFromWildcard() falls back to LIKE queries against the raw sub-tag table (lines 63-78), which is slower but necessary for pattern matching arbitrary substrings.

Can the tag search caches become corrupted, and how do I fix them?

While the caches are designed to stay synchronized through the AddTags() method, bulk imports or tag sibling operations can occasionally cause drift. Users can rebuild all caches via the Tag text search cache menu item in ClientGUI.py (lines 3406-3408), which truncates the tables and forces a fresh population from the master tag registry.

Why does Hydrus store integer sub-tags separately from text sub-tags?

The integer sub-tag cache enables numeric range queries (e.g., width>1920) to use standard integer comparison operators and indices. Without this separation, numeric comparisons would require casting text fields at query time, preventing index usage and forcing full table scans. The CanCacheInteger() check (lines 35-38) ensures only valid 64-bit integers populate this specialized structure.

What is the performance impact of the searchable-sub-tag map?

The searchable-sub-tag map eliminates redundant computation of the "searchable" version of tags—created by ConvertSubtagToSearchable() in ClientSearchTagContext.py—which involves lower-casing and punctuation removal. By caching this transformation once per tag during insertion, the query engine avoids processing the same normalization logic millions of times during autocomplete operations, significantly reducing CPU overhead per keystroke.

{
  "mcpServers": {
    "instagit": {
      "command": "npx",
      "args": ["-y", "instagit@latest"]
    }
  }
}