SQLite Index Usage with Case Insensitivity and Expression-Based Queries

How SQLite Index Selection Interacts with Collation Settings and Expression Matching

Fundamental Principles of Index Utilization in Case-Sensitive and Function-Based Contexts

SQLite’s query planner selects indexes based on precise matches between the expressions and collations defined in the index and those used in queries. When an index is created on a column or expression, the database records metadata such as collation sequence and the exact syntactic form of the indexed expression. For the index to be considered usable, the query’s predicates must align with this metadata. This alignment includes both the literal expression structure (e.g., Lower(foo)) and the collation type (e.g., NOCASE, BINARY).

For instance, consider an index created as CREATE INDEX idx ON t(foo COLLATE NOCASE). This index will only be used for queries where the foo column is compared using the NOCASE collation. If a query applies Lower(foo) or uses a different collation (e.g., BINARY), the index will not be utilized because the collation or expression structure mismatches. Similarly, an index on Lower(foo) is specific to queries that explicitly use Lower(foo) in their predicates. SQLite does not automatically infer that Lower(foo) is equivalent to foo COLLATE NOCASE or vice versa, even if they achieve similar results.

This behavior stems from SQLite’s deterministic approach to index selection. The query planner does not perform deep semantic analysis of functions or collations to determine equivalence. Instead, it relies on syntactic and metadata matches. For example, if a user-defined function MY_LOWER() is created, SQLite cannot assume it behaves identically to the built-in Lower() function. Thus, indexes on Lower(foo) will not be used for queries involving MY_LOWER(foo), even if they are functionally identical. This rigidity ensures predictability but requires careful schema and query design.

Common Misconfigurations Leading to Index Underutilization in Case-Insensitive Scenarios

1. Collation Mismatch Between Index and Query:
   The most frequent cause of index underutilization is a discrepancy between the collation specified in the index and the collation used in the query. For example, an index created with CREATE INDEX idx ON t(foo COLLATE NOCASE) will not be used for a query like SELECT * FROM t WHERE foo = 'value' if the default collation of the column is BINARY. The query must explicitly specify COLLATE NOCASE in the predicate (e.g., WHERE foo = 'value' COLLATE NOCASE) to trigger index usage. This requirement is often overlooked, leading to unintended full-table scans.

2. Implicit Collation Inheritance from Column Definitions:
   Columns in SQLite inherit their collation from the table definition unless explicitly overridden. If a column is defined as TEXT COLLATE NOCASE, any index on that column will implicitly use the NOCASE collation. However, queries that apply a different collation (e.g., WHERE foo COLLATE BINARY = 'Value') will not use the index. Developers may mistakenly assume that the index’s collation is "adaptive" to the query’s collation, which is not the case.

3. Function-Based Indexes Require Exact Expression Replication:
   SQLite allows indexes on expressions (e.g., CREATE INDEX idx ON t(Lower(foo))). However, the query must use the exact same expression in its predicate to leverage the index. A query with WHERE Lower(foo) = 'value' will use the index, but a query with WHERE Upper(foo) = 'VALUE' or WHERE foo = 'Value' COLLATE NOCASE will not, even if the logical result is equivalent. This rigidity necessitates careful alignment between query predicates and indexed expressions.

4. Overlooking Collation Propagation in Joins and Subqueries:
   When columns from different tables are compared in a join, SQLite uses the collation of the left-hand operand. If the indexed column has a different collation than the column it is being joined against, the index may not be used. For example, if tableA.foo has a NOCASE index and tableB.bar uses BINARY collation, a join predicate like ON tableA.foo = tableB.bar will not use the NOCASE index unless the collation is explicitly harmonized in the query.

Comprehensive Strategies for Aligning Index Definitions with Query Requirements

Step 1: Audit Existing Indexes and Query Patterns

Begin by cataloging all indexes and their associated collations or expressions. Use the sqlite_master table to retrieve index definitions:

SELECT name, sql FROM sqlite_master WHERE type = 'index';

Simultaneously, analyze query logs or application code to identify frequently executed queries and their predicates. Look for patterns such as repeated use of Lower(), Upper(), or explicit collations.

Step 2: Standardize Collation at the Table Level When Possible

If case-insensitive comparisons are the norm for a column, define the column with COLLATE NOCASE during table creation:

CREATE TABLE t (foo TEXT COLLATE NOCASE, ...);

This ensures that all indexes on foo inherit the NOCASE collation by default. Queries using WHERE foo = 'value' will automatically use the NOCASE collation, allowing existing indexes to be utilized without additional syntax.

Trade-off: Case-sensitive queries against this column will require explicit COLLATE BINARY clauses and will not benefit from indexes. Evaluate the frequency of case-sensitive searches to determine if this trade-off is acceptable.

Step 3: Create Function-Specific Indexes for Expression-Heavy Queries

For queries that use functions like Lower(foo), create an index on the exact expression:

CREATE INDEX idx_lower_foo ON t(Lower(foo));

This index will be used exclusively for queries with WHERE Lower(foo) = 'value'. Note that such indexes increase storage overhead and write latency, so prioritize them for performance-critical queries.

Optimization: If multiple columns are involved in a function (e.g., Lower(foo || bar)), consider creating a composite index on the concatenated expression:

CREATE INDEX idx_lower_foobar ON t(Lower(foo || bar));

Step 4: Explicitly Match Collations in Queries to Index Definitions

When querying a column with a non-default collation, explicitly specify the collation in the predicate:

SELECT * FROM t WHERE foo = 'value' COLLATE NOCASE;

This ensures the query planner recognizes the collation match with the index. Use EXPLAIN QUERY PLAN to verify index usage:

EXPLAIN QUERY PLAN SELECT * FROM t WHERE foo = 'value' COLLATE NOCASE;

The output should indicate USING INDEX idx_nocase_foo if the collation matches.

Step 5: Utilize Covering Indexes to Avoid Collation Conflicts in Projections

If a query selects multiple columns, create a covering index that includes all required columns, using the appropriate collation:

CREATE INDEX idx_covering ON t(foo COLLATE NOCASE, bar, baz);

This allows SQLite to satisfy the query entirely from the index, avoiding table scans. Ensure the leading column of the index matches the collation used in the query’s predicates.

Step 6: Benchmark and Compare Performance with Alternative Collation Strategies

For ambiguous scenarios where both case-sensitive and case-insensitive queries are common, benchmark two approaches:

1. Dual Indexes:
   Create separate indexes for BINARY and NOCASE collations:

   CREATE INDEX idx_binary ON t(foo COLLATE BINARY);
   CREATE INDEX idx_nocase ON t(foo COLLATE NOCASE);
   

   Queries will use the appropriate index based on their collation clause. This approach maximizes flexibility but doubles index storage and maintenance costs.

2. Forced Collation at Query Time:
   Use a single index with the predominant collation and force the less common collation in queries:

   -- Predominantly NOCASE
   CREATE INDEX idx_nocase ON t(foo COLLATE NOCASE);
   -- Case-sensitive query
   SELECT * FROM t WHERE foo = 'value' COLLATE BINARY;
   

   This may result in full scans for the less common case but reduces index overhead.

Step 7: Address Edge Cases with Partial Indexes or Materialized Views

For rarely executed case-sensitive queries against a predominantly case-insensitive column, consider partial indexes:

CREATE INDEX idx_case_sensitive_partial ON t(foo) WHERE foo COLLATE BINARY = foo;

This index includes only rows where foo has non-lowercase characters, reducing its size. Alternatively, use a materialized view with a BINARY collation for case-sensitive searches:

CREATE TABLE t_case_sensitive AS SELECT foo COLLATE BINARY AS foo FROM t;
CREATE INDEX idx_cs_foo ON t_case_sensitive(foo);

Step 8: Monitor and Optimize Index Usage Over Time

Regularly review query plans using EXPLAIN QUERY PLAN and the sqlite_stat1 table to ensure indexes remain effective as data volumes and query patterns evolve. Drop unused indexes to reduce storage and insert/update overhead.

By systematically aligning index definitions with query requirements and understanding SQLite’s collation and expression matching rules, developers can ensure optimal query performance while accommodating both case-sensitive and case-insensitive use cases.