# How the Hydrus Tag Search System Optimizes Queries with Caching

> Discover how Hydrus optimizes tag searches with four specialized SQLite caches. Learn how pre-computed sub-tags and wildcard expansions boost performance and eliminate slow table scans.

- Repository: [Hydrus Network Developer/hydrus](https://github.com/hydrusnetwork/hydrus)
- Tags: internals
- Published: 2026-03-03

---

**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`](https://github.com/hydrusnetwork/hydrus/blob/main/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:

```python
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`](https://github.com/hydrusnetwork/hydrus/blob/main/ClientDBTagSearch.py)) synchronously updates all four cache families. The method inserts tag mappings and FTS4 entries using `INSERT OR IGNORE` to maintain idempotency:

```python

# 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`](https://github.com/hydrusnetwork/hydrus/blob/main/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`](https://github.com/hydrusnetwork/hydrus/blob/main/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`](https://github.com/hydrusnetwork/hydrus/blob/main/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`](https://github.com/hydrusnetwork/hydrus/blob/main/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.