Optimizing Date Range Queries with LIKE and Indexes in SQLite

Understanding Collation Mismatch in LIKE Queries for Date Ranges

Issue Overview
The core challenge revolves around efficiently querying date ranges stored in a text-based timestamp column using SQLite’s LIKE operator. The goal is to retrieve records matching partial date prefixes (e.g., "2010-%" for all entries in 2010) while leveraging indexes to avoid full table scans. The problem arises from SQLite’s default collation behavior and the interaction between LIKE patterns, column data types, and index configurations.

When a LIKE operator is used with a wildcard suffix (e.g., timestamp LIKE '2020-05-%'), SQLite should theoretically utilize an index scan if the pattern matches a fixed prefix. However, the default case-sensitive (BINARY) collation of the column and its index conflicts with the LIKE operator’s implicit case-insensitive (NOCASE) collation. This mismatch forces SQLite to perform a sequential scan instead of an index seek.

Additional complications include data type affinity discrepancies. For instance, declaring a column as datetime assigns it NUMERIC affinity, which may lead to unintended type conversions during string comparisons. This further inhibits index usage when querying with text-based patterns.

The discussion highlights two viable paths for resolution:

1. Aligning collation settings between the column, index, and query.
2. Using alternative operators (GLOB, range conditions) that bypass collation mismatches.

Collation Settings and Data Type Affinity Conflicts

Possible Causes

1. Collation Mismatch Between Index and Query:

   • SQLite indexes default to BINARY collation. The LIKE operator uses NOCASE collation implicitly. When the index and query collations differ, SQLite cannot utilize the index for prefix searches.
   • Example: A timestamp LIKE '2020-05-%' query requires NOCASE collation for index compatibility, but the index is BINARY by default.

2. Data Type Affinity Interference:

   • Columns declared with type datetime inherit NUMERIC affinity. Storing text-based timestamps in such columns may trigger silent type conversions during comparisons, rendering string-based LIKE patterns ineffective.
   • Example: A timestamp stored as '2020-05-01' in a NUMERIC-affinity column may be treated as a number (e.g., 20200501), breaking prefix-based string matching.

3. Case Sensitivity in Pattern Matching:

   • LIKE performs case-insensitive matches by default, conflicting with case-sensitive indexes. This forces SQLite to disregard the index unless collations are explicitly aligned.

4. Operator Choice:

   • GLOB is case-sensitive and uses BINARY collation, making it incompatible with LIKE-style queries unless the index is explicitly configured for BINARY collation.

Resolving Collation Mismatches and Enabling Index Usage

Troubleshooting Steps, Solutions & Fixes

1. Align Collation Settings for Index and Column

Step 1: Modify Index Collation
Create an index with NOCASE collation to match the LIKE operator’s implicit collation:

DROP INDEX idx;
CREATE INDEX idx ON t(timestamp COLLATE NOCASE);

This allows the query planner to recognize the index as compatible with LIKE patterns.

Step 2: Adjust Column Collation
Define the timestamp column with NOCASE collation during table creation:

CREATE TABLE t (
    timestamp TEXT COLLATE NOCASE,
    data
);

Any index created on this column will inherit NOCASE collation by default, ensuring alignment with LIKE queries.

Verification:
Use EXPLAIN QUERY PLAN to confirm index usage:

EXPLAIN QUERY PLAN
SELECT * FROM t WHERE timestamp LIKE '2020-05-%';

The output should indicate SEARCH TABLE t USING INDEX idx, confirming the index is utilized.

2. Use GLOB for Case-Sensitive Prefix Matching

Step 1: Replace LIKE with GLOB
For case-sensitive prefix queries, use GLOB with a BINARY collation index:

SELECT * FROM t WHERE timestamp GLOB '2020-05-*';

Step 2: Ensure BINARY Collation Compatibility
If the column and index use default BINARY collation, GLOB will leverage the index:

CREATE INDEX idx ON t(timestamp);  -- Default collation is BINARY

Verification:
Check the query plan to ensure SEARCH instead of SCAN:

EXPLAIN QUERY PLAN
SELECT * FROM t WHERE timestamp GLOB '2020-05-*';

3. Use Range Conditions for Index Optimization

Step 1: Convert Prefix Patterns to Range Queries
Replace LIKE 'YYYY-MM-%' with explicit range conditions:

SELECT * FROM t
WHERE timestamp >= '2020-05' AND timestamp < '2020-06';

This approach bypasses collation issues and guarantees index usage with any collation.

Step 2: Automate Range Generation
Use application logic or SQLite date functions to dynamically compute range boundaries:

SELECT * FROM t
WHERE timestamp >= DATE('2020-05-01')
  AND timestamp < DATE('2020-05-01', '+1 month');

Advantages:

• Avoids collation conflicts entirely.
• Works with all data types and indexes.

4. Configure Case-Sensitive LIKE Behavior

Step 1: Enable Case-Sensitive LIKE
Set the case_sensitive_like PRAGMA to force LIKE to use BINARY collation:

PRAGMA case_sensitive_like = ON;

Step 2: Rebuild Indexes for BINARY Collation
Ensure indexes are rebuilt without NOCASE collation:

DROP INDEX idx;
CREATE INDEX idx ON t(timestamp);  -- BINARY collation

Limitations:

• Affects all LIKE operations globally.
• Requires application-level adjustments to handle case sensitivity.

5. Correct Data Type Affinity Issues

Step 1: Use TEXT Affinity for Timestamp Columns
Declare the timestamp column as TEXT to prevent NUMERIC affinity:

CREATE TABLE t (
    timestamp TEXT,  -- Explicit TEXT affinity
    data
);

Step 2: Migrate Existing Data
If the column previously had NUMERIC affinity, update stored values to text:

UPDATE t SET timestamp = CAST(timestamp AS TEXT);

Verification:
Ensure values are stored as strings (e.g., '2020-05-01') rather than numbers.

6. Comprehensive Example Workflow

1. Create Table with Correct Affinity and Collation:

   CREATE TABLE t (
       timestamp TEXT COLLATE NOCASE,
       data
   );
   

2. Populate Data:

   INSERT INTO t VALUES
       ('2021-08-02', 'date1'),
       ('2021-08-23', 'date2'),
       ('2021-09-01', 'date3');
   

3. Create NOCASE Index:

   CREATE INDEX idx ON t(timestamp);
   

4. Execute Optimized Query:

   SELECT * FROM t WHERE timestamp LIKE '2021-08-%';
   

5. Validate Query Plan:

   EXPLAIN QUERY PLAN
   SELECT * FROM t WHERE timestamp LIKE '2021-08-%';
   -- Output: `SEARCH TABLE t USING INDEX idx (timestamp>? AND timestamp<?)`
   

Final Recommendations

1. Prefer Range Conditions: Use >= and < for date ranges to ensure index usage regardless of collation settings.
2. Standardize Collation: Align column and index collation with query requirements (NOCASE for LIKE, BINARY for GLOB).
3. Validate Data Types: Ensure timestamp columns use TEXT affinity to avoid unintended type conversions.
4. Leverage PRAGMAs Sparingly: Use case_sensitive_like only if case sensitivity is critical, as it affects all queries.

By systematically addressing collation mismatches, data type affinity, and query patterns, SQLite can efficiently leverage indexes for date range queries using LIKE or alternative operators.