FTS5 Performance Issues: = vs. MATCH, INDEX 0, and “error: no such column”

FTS5 Query Performance Discrepancy: = Operator vs. MATCH Operator

The SQLite FTS5 extension is designed to provide full-text search capabilities, allowing users to perform sophisticated text searches on large datasets. According to the FTS5 documentation, the = operator and the MATCH operator are described as equivalent in certain contexts. However, in practice, significant performance discrepancies arise when using these operators, particularly on large datasets with unique values.

When querying an FTS5 table with 10,000,000 records, the MATCH operator performs approximately 25,000 times faster than the = operator. This is counterintuitive, as one would expect the = operator, which performs a simple value comparison, to be faster than the MATCH operator, which is designed for more complex text searches. The performance difference is stark: the MATCH operator averages 6.254e-05 seconds per query, while the = operator averages 1.599 seconds per query.

The discrepancy is further highlighted by the query plans generated by SQLite. When using the MATCH operator, SQLite reports using INDEX 1, which suggests an efficient index-based search. However, when using the = operator, SQLite reports using INDEX 0, which indicates a linear scan of the entire table. This behavior is unexpected, as one would assume that the = operator would leverage an index for faster lookups, especially given that the column contains unique values.

The performance issues with the = operator become particularly problematic in use cases where an FTS5 table is used to store both text-searchable data and primitive values (e.g., integers, dates). In such scenarios, the FTS5 table is often related to a "main" table via a primary key. Retrieving records from the FTS5 table using the = operator on the primary key column can lead to extremely slow query execution times, especially as the table grows in size. For example, a query that should take a few minutes to execute might run for hours without completing.

The performance discrepancy between the = and MATCH operators raises questions about the internal workings of SQLite’s FTS5 extension. Specifically, it suggests that the = operator may not be leveraging the FTS5 index effectively, leading to inefficient query execution. This behavior is particularly problematic for users who rely on FTS5 tables to store and retrieve large datasets with unique values.

Interrupted Write Operations Leading to Index Corruption

The performance issues with the = operator in FTS5 tables are compounded by the fact that SQLite’s FTS5 extension does not support traditional indexing on non-FTS5 columns. This limitation forces users to store all columns in an FTS5 table as FTS5-indexed columns, even when those columns contain primitive values that do not require full-text search capabilities. As a result, queries that involve simple value comparisons (e.g., using the = operator) may not benefit from the efficient indexing mechanisms that are typically available in non-FTS5 tables.

The lack of traditional indexing in FTS5 tables can lead to significant performance degradation, especially when querying large datasets. In the case of the = operator, SQLite appears to perform a linear scan of the entire table, even when the column being queried contains unique values. This behavior is inconsistent with the expected performance of a simple value comparison, which should ideally leverage an index for efficient lookups.

The issue is further complicated by the fact that SQLite’s query plan indicates the use of INDEX 0 when the = operator is used. While INDEX 0 is typically associated with a linear scan, it is unclear whether this indicates an unindexed operation or an inefficient use of an existing index. In either case, the result is the same: queries that use the = operator on FTS5 tables with large datasets are significantly slower than those that use the MATCH operator.

The performance issues with the = operator are particularly problematic for users who need to relate records in an FTS5 table to records in a "main" table via a primary key. In such cases, the lack of efficient indexing on the primary key column in the FTS5 table can lead to extremely slow query execution times, especially as the dataset grows. This limitation effectively forces users to choose between the full-text search capabilities of FTS5 and the efficient indexing mechanisms available in non-FTS5 tables.

Implementing PRAGMA journal_mode and Database Backup

To address the performance issues associated with the = operator in FTS5 tables, users can consider several strategies. One approach is to use the MATCH operator instead of the = operator for queries that involve simple value comparisons. While this may seem counterintuitive, the MATCH operator appears to leverage the FTS5 index more effectively, leading to significantly faster query execution times. For example, replacing the = operator with the MATCH operator in queries that involve primary key lookups can reduce query execution times from several hours to a few minutes.

Another approach is to use external content tables in conjunction with FTS5 tables. External content tables allow users to store the full-text searchable data in an FTS5 table while storing the primitive values in a separate non-FTS5 table. This approach allows users to leverage the efficient indexing mechanisms available in non-FTS5 tables for queries that involve simple value comparisons, while still benefiting from the full-text search capabilities of FTS5. For example, users can store the primary key and other primitive values in a non-FTS5 table and use a foreign key to relate these values to the corresponding records in the FTS5 table.

In addition to using external content tables, users can also consider using the UNINDEXED option to exclude certain columns from the FTS5 index. While this option does not allow users to create traditional indexes on FTS5 columns, it can help reduce the size of the FTS5 index, potentially improving query performance. However, this approach is limited by the fact that the UNINDEXED option only applies to columns that do not require full-text search capabilities.

Finally, users should be aware of the potential issues associated with using the MATCH operator on negative numbers. The FTS5 extension interprets dashes in the fields of a MATCH query as a column filter, which can lead to errors when querying negative numbers. To avoid these errors, users should enclose negative numbers in double quotes inside single quotes. For example, the query SELECT * FROM test WHERE content MATCH '"-1"' will work correctly, while the query SELECT * FROM test WHERE content MATCH '-1' will result in an error.

In conclusion, the performance issues associated with the = operator in FTS5 tables can be addressed by using the MATCH operator, external content tables, and the UNINDEXED option. While these strategies may require some adjustments to the database schema and query logic, they can significantly improve query performance, especially for large datasets. Additionally, users should be aware of the potential issues associated with using the MATCH operator on negative numbers and take appropriate steps to avoid errors. By implementing these strategies, users can leverage the full-text search capabilities of FTS5 while maintaining efficient query performance.