Issue Overview: FTS5 Table Join Performance and Correctness with ID Columns

When integrating SQLite’s FTS5 virtual tables with conventional relational tables, developers often face critical decisions regarding query structure and performance optimization. A recurring challenge arises when attempting to join an FTS5 virtual table containing an ID column to a standard table’s primary key. The core dilemma revolves around whether to use equality operators (=) or full-text search predicates (MATCH) for these joins, given FTS5’s unique indexing behavior and schema constraints.

FTS5 virtual tables are optimized for text search operations rather than relational joins. Unlike standard SQLite tables, they lack traditional indexes on non-content columns and enforce no foreign key constraints. This creates ambiguity when developers attempt to use FTS5 tables in JOIN operations that would normally benefit from primary/foreign key relationships in conventional relational databases.

Consider this schema implementation:

CREATE TABLE post(id INTEGER PRIMARY KEY, author TEXT);
CREATE VIRTUAL TABLE post_fts USING fts5(id UNINDEXED, body);

The post_fts.id column stores foreign keys referencing post.id, but FTS5’s architecture introduces three fundamental constraints:

1. No Secondary Indexes: FTS5 virtual tables cannot create conventional indexes on columns explicitly declared as UNINDEXED
2. Tokenization Rules Apply: All FTS5 columns undergo tokenization unless marked UNINDEXED, affecting how MATCH operations interpret values
3. Rowid Semantics: FTS5 tables contain an implicit rowid column that behaves differently from conventional INTEGER PRIMARY KEY declarations

When executing join operations between post and post_fts, two query patterns emerge as potential candidates:

Equality-Based Join

SELECT post.id 
FROM post 
INNER JOIN post_fts ON post_fts.id = post.id;

MATCH-Based Join

SELECT post.id 
FROM post 
INNER JOIN post_fts ON post_fts.id MATCH post.id;

The critical questions become:

• Does MATCH leverage FTS5’s internal indexing structures more effectively than =?
• What data integrity risks emerge when using text search operations on numeric ID columns?
• How does FTS5’s tokenization affect numeric ID comparisons?
• Can alternative schema designs eliminate this dilemma entirely?

Possible Causes: Why ID Comparison Methods Yield Different Results

1. FTS5 Column Storage and Tokenization Mismatch

FTS5 treats all columns as text search vectors by default, applying tokenization rules even to numeric identifiers. When developers store numeric IDs in FTS5 columns without using the UNINDEXED attribute, unexpected behavior occurs:

• Numeric Tokenization: The FTS5 tokenizer converts "123" to a single token, but "123-456" becomes two tokens ("123", "456")
• MATCH Operator Logic: post_fts.id MATCH '123' matches any row containing token "123" in the id column, not exact numeric equality
• Equality Check Limitations: post_fts.id = 123 performs a literal string comparison against stored values, which may not match numeric representations

Example: Storing ID "123 " (with trailing space) in post_fts.id causes:

• MATCH 123 → Match (tokenization ignores whitespace)
• = 123 → No match (string "123 " ≠ integer 123)

2. Index Utilization Differences in FTS5 Architecture

FTS5 maintains inverted indexes optimized for text search, not equality comparisons:

• MATCH Uses FTS Index: The MATCH operator leverages FTS5’s full-text index for rapid token lookup
• Equality Scans Entire Table: = comparisons with non-UNINDEXED columns require full table scans
• UNINDEXED Column Penalty: Declaring id UNINDEXED in FTS5 disables tokenization but provides no performance benefits for equality joins

Performance characteristics differ dramatically:

3. Contentless Table Design Tradeoffs

External content tables introduce alternative indexing strategies but add complexity:

CREATE VIRTUAL TABLE post_fts USING fts5(body, content='post', content_rowid='id');

• Contentless FTS5: Stores only search indexes, referencing original table data
• Rowid Synchronization: Requires strict alignment between FTS5’s rowid and the base table’s ID
• Update Propagation: Requires triggers to maintain consistency between base and FTS5 tables

In this configuration, joining via post_fts.rowid = post.id becomes efficient, but introduces new maintenance overhead.

Troubleshooting Steps, Solutions & Fixes: Optimizing FTS5 ID Joins

Step 1: Validate Data Types and Storage Formats

Execute diagnostic queries to inspect actual data storage in FTS5:

-- Check FTS5 column storage format
SELECT typeof(id), quote(id) FROM post_fts LIMIT 5;

-- Compare against base table
SELECT typeof(id), quote(id) FROM post LIMIT 5;

Common Issues Found:

• FTS5 storing IDs as TEXT while base table uses INTEGER
• Hidden whitespace in FTS5 ID values (e.g., ‘123\n’)
• Tokenization splitting hyphenated IDs (e.g., ‘123-456’ → tokens ‘123’ and ‘456’)

Resolution Tactics:

1. Enforce UNINDEXED Column Type
   Recreate FTS5 table with explicit type casting:

   CREATE VIRTUAL TABLE post_fts USING fts5(id UNINDEXED, body);
   

2. Normalize ID Storage
   Use CAST during FTS5 population:

   INSERT INTO post_fts(id, body) 
   VALUES (CAST(post.id AS TEXT), post.body);
   

3. Trim Whitespace
   Apply TRIM() during ETL processes:

   UPDATE post_fts SET id = TRIM(id);
   

Step 2: Analyze Query Plans with EXPLAIN

Compare execution plans for both join methods:

-- Equality join plan
EXPLAIN QUERY PLAN
SELECT post.id FROM post 
INNER JOIN post_fts ON post_fts.id = post.id;

-- MATCH join plan
EXPLAIN QUERY PLAN  
SELECT post.id FROM post
INNER JOIN post_fts ON post_fts.id MATCH post.id;

Expected Output Analysis:

• Equality Join
  SCAN TABLE post_fts → Full table scan
  SEARCH TABLE post USING INTEGER PRIMARY KEY

• MATCH Join
  SCAN TABLE post_fts VIRTUAL TABLE INDEX 0:M → FTS5 index usage
  SEARCH TABLE post USING INTEGER PRIMARY KEY

Performance Optimization:

• If MATCH shows index usage but returns incorrect results, implement type validation:

  SELECT post.id 
  FROM post 
  INNER JOIN post_fts 
    ON post_fts.id MATCH CAST(post.id AS TEXT) 
    AND post_fts.id = post.id;
  

• This combines FTS5 index speed with equality accuracy

Step 3: Implement External Content Tables

Redesign schema to leverage contentless FTS5 tables with synchronized rowids:

-- Base table with rowid alias
CREATE TABLE post(id INTEGER PRIMARY KEY, author TEXT);

-- Contentless FTS5 table
CREATE VIRTUAL TABLE post_fts USING fts5(
  body, 
  content='post', 
  content_rowid='id'
);

-- Maintenance triggers
CREATE TRIGGER post_ai AFTER INSERT ON post BEGIN
  INSERT INTO post_fts(rowid, body) VALUES (new.id, new.body);
END;

CREATE TRIGGER post_ad AFTER DELETE ON post BEGIN
  INSERT INTO post_fts(post_fts, rowid, body) VALUES('delete', old.id, old.body);
END;

CREATE TRIGGER post_au AFTER UPDATE ON post BEGIN
  INSERT INTO post_fts(post_fts, rowid, body) VALUES('delete', old.id, old.body);
  INSERT INTO post_fts(rowid, body) VALUES (new.id, new.body);
END;

Join Execution:

SELECT post.id 
FROM post 
INNER JOIN post_fts ON post_fts.rowid = post.id;

Benefits:

• Eliminates redundant ID storage
• Enables efficient integer-based joins via rowid
• Automates index maintenance
• Avoids tokenization issues with ID columns

Migration Path for Existing Data:

1. Export existing FTS5 data:

   CREATE TEMP TABLE fts_backup AS SELECT * FROM post_fts;
   

2. Recreate as contentless table
3. Re-insert data through base table triggers:

   INSERT INTO post(id, author, body) 
   SELECT id, author, body FROM post 
   JOIN fts_backup ON post.id = fts_backup.id;
   

Step 4: Benchmark Alternative Join Strategies

Measure actual performance with realistic datasets:

Test Harness Setup:

-- Populate 1M rows
WITH RECURSIVE
  cnt(x) AS (VALUES(1) UNION ALL SELECT x+1 FROM cnt WHERE x<1000000)
INSERT INTO post(id, author)
SELECT x, 'author_' || (x % 100) FROM cnt;

INSERT INTO post_fts(id, body) 
SELECT id, 'Body content for post ' || id FROM post;

Execution Time Tests:

1. Equality Join

   SELECT COUNT(*) FROM post INNER JOIN post_fts ON post_fts.id = post.id;
   

   Expected: 1000-2000ms (full scan)

2. MATCH Join

   SELECT COUNT(*) FROM post INNER JOIN post_fts ON post_fts.id MATCH post.id;
   

   Expected: 10-50ms (index scan)
   Risk: False positives if IDs contain tokenizable characters

3. Rowid Join (Contentless)

   SELECT COUNT(*) FROM post INNER JOIN post_fts ON post_fts.rowid = post.id;
   

   Expected: 5-20ms (integer index scan)

Result Interpretation:

• MATCH provides speed but risks inaccuracy
• Contentless rowid joins offer optimal performance and accuracy
• Equality joins should be avoided except for small tables

Step 5: Implement Hybrid Validation Queries

For systems requiring MATCH speed without contentless migration:

SELECT post.id 
FROM post
INNER JOIN (
  SELECT rowid, id FROM post_fts 
  WHERE id MATCH CAST(:search_id AS TEXT)
) AS fts_subset
ON post.id = fts_subset.id
AND post.id = :search_id;

Breakdown:

1. Inner post_fts subquery uses MATCH for fast index scan
2. Outer join enforces exact equality on base table ID
3. Parameter binding ensures type consistency

Performance Characteristics:

• FTS5 index narrows candidate rows rapidly
• Final equality check on base table guarantees accuracy
• Adds minor overhead from nested loop join

Final Recommendation Matrix

Implementing these strategies requires careful analysis of data types, query patterns, and performance requirements. Contentless tables with rowid-based joins generally provide the optimal balance of speed and reliability, while hybrid approaches offer transitional solutions for legacy systems. Always validate with EXPLAIN QUERY PLAN and real-world benchmarking before finalizing architectural changes.