Optimizing SQLite Time Range Queries: LIKE vs. Column Splitting vs. BETWEEN with Indexing

Understanding the Performance Characteristics of Time-Based Pattern Matching

Issue Overview
The challenge revolves around efficiently querying time-based data stored in an SQLite database when searching for records matching a specific minute within an hour (e.g., all times between 21:45:00 and 21:45:59). Three approaches are proposed:

1. Using the LIKE operator with a trailing wildcard ('21:45:%').
2. Using the LIKE operator with underscore wildcards ('21:45:__').
3. Splitting the time column into three separate integer columns (hour, minute, second) and querying with exact matches (WHERE hour = 21 AND minute = 45).
4. A fourth approach suggested in the discussion involves using the BETWEEN operator with boundary values (BETWEEN '21:45:00' AND '21:45:99') combined with proper indexing.

The core issue is determining which method provides optimal query performance while balancing schema design complexity, storage overhead, and index utilization. Time-based pattern matching often involves trade-offs between string manipulation, numerical comparisons, and index efficiency. SQLite’s flexible typing system and lack of native time/date data types add complexity, as time values are typically stored as strings or split into integers.

Key factors influencing performance include:

• Index Usage: Whether the query can leverage indexes to avoid full-table scans.
• Data Representation: String-based time values versus split integer components.
• Wildcard Behavior: How the LIKE operator’s wildcards (%, _) interact with indexes.
• Comparison Semantics: String comparisons (lexicographical ordering) versus integer comparisons.

For example, LIKE '21:45:%' performs a pattern match on a string column, which may or may not utilize an index depending on the wildcard position. Splitting the time into separate columns allows exact matches on integers, which are faster to compare but require a composite index. The BETWEEN approach treats time as a string but uses range comparisons, which can efficiently use indexes if formatted correctly.

Factors Contributing to Suboptimal Query Performance

Possible Causes

1. Inefficient Use of Wildcards in LIKE Queries:

   • The LIKE operator with a leading wildcard (%) prevents SQLite from using indexes on the column. Trailing wildcards (e.g., '21:45:%') allow partial index usage, but only if the collation sequence supports it.
   • The _ wildcard matches exactly one character, so '21:45:__' matches times with exactly two digits for seconds (e.g., 21:45:00 to 21:45:99). However, this still requires a full scan of the index if the column is not properly indexed.

2. Non-Sargable Predicates:

   • Queries that apply functions or operations to the column (e.g., SUBSTR(time, 1, 5) = '21:45') prevent index usage. Splitting the time into separate columns avoids this issue but introduces schema complexity.

3. Suboptimal Index Design:

   • A single-column index on the time string may not be usable for BETWEEN or LIKE queries if the time format is inconsistent (e.g., '9:5:3' vs. '09:05:03').
   • Composite indexes on split columns (hour, minute, second) must be carefully ordered (e.g., (hour, minute)) to support queries filtering on hour and minute.

4. Data Type Mismatch:

   • Storing time as a string (e.g., '21:45:29') allows lexical comparisons but may lead to incorrect results if the format varies (e.g., '21:45:9' vs. '21:45:09').
   • Integer-based columns enforce consistency but require additional storage and JOIN overhead if time components are split across tables.

5. Collation and Sorting Overhead:

   • String comparisons depend on the collation sequence defined for the column. The default BINARY collation treats '21:45:99' as greater than '21:45:59', which could cause unexpected results in BETWEEN queries.

Strategies for Resolving Time-Based Query Performance Issues

Troubleshooting Steps, Solutions & Fixes

Step 1: Standardize Time Representation

Ensure all time values are stored in a consistent format. Use HH:MM:SS with leading zeros (e.g., '09:05:03' instead of '9:5:3'). This enables reliable string comparisons and proper index utilization.

Example Schema for String-Based Time:

CREATE TABLE events (
    id INTEGER PRIMARY KEY,
    event_time TEXT CHECK (event_time GLOB '[0-2][0-9]:[0-5][0-9]:[0-5][0-9]')
);

CREATE INDEX idx_event_time ON events(event_time);

Example Schema for Split Columns:

CREATE TABLE events (
    id INTEGER PRIMARY KEY,
    hour INTEGER CHECK (hour BETWEEN 0 AND 23),
    minute INTEGER CHECK (minute BETWEEN 0 AND 59),
    second INTEGER CHECK (second BETWEEN 0 AND 59)
);

CREATE INDEX idx_hour_minute ON events(hour, minute);

Step 2: Analyze Query Plans with EXPLAIN QUERY PLAN

Use SQLite’s EXPLAIN QUERY PLAN to determine whether indexes are being used:

For LIKE '21:45:%':

EXPLAIN QUERY PLAN
SELECT * FROM events WHERE event_time LIKE '21:45:%';

• If the output shows SCAN TABLE events, the index is not being used.
• If it shows SEARCH TABLE events USING INDEX idx_event_time (event_time>? AND event_time<?), the index is partially used.

For WHERE hour = 21 AND minute = 45:

EXPLAIN QUERY PLAN
SELECT * FROM events WHERE hour = 21 AND minute = 45;

• Look for SEARCH TABLE events USING INDEX idx_hour_minute (hour=? AND minute=?).

Step 3: Optimize Indexes for Chosen Approach

String-Based Time with BETWEEN:
Create an index on the event_time column and ensure queries use range comparisons:

SELECT * FROM events 
WHERE event_time BETWEEN '21:45:00' AND '21:45:59';

• This query will efficiently use the idx_event_time index.

Split Columns with Composite Index:
A composite index on (hour, minute) allows the database to quickly locate rows matching both criteria without scanning the entire table.

Step 4: Benchmark Competing Approaches

Use real-world data to test each method:

Test Script for String-Based Time:

-- Populate table with sample data
INSERT INTO events (event_time)
SELECT 
    printf('%02d:%02d:%02d', 
           abs(random() % 24), 
           abs(random() % 60), 
           abs(random() % 60)) 
FROM generate_series(1, 1000000);

-- Benchmark query
.timer ON
SELECT COUNT(*) FROM events 
WHERE event_time BETWEEN '21:45:00' AND '21:45:59';

Test Script for Split Columns:

INSERT INTO events (hour, minute, second)
SELECT 
    abs(random() % 24),
    abs(random() % 60),
    abs(random() % 60)
FROM generate_series(1, 1000000);

SELECT COUNT(*) FROM events 
WHERE hour = 21 AND minute = 45;

Step 5: Address Edge Cases and Collation

• Padding Issues: Ensure all string-based times are padded with leading zeros.
• Collation: Use COLLATE BINARY for string comparisons to avoid locale-specific sorting.
• Index Coverage: For split columns, include all frequently filtered columns in the composite index.

Final Recommendation

1. Use BETWEEN with Indexed String Column: This approach provides the best balance between simplicity and performance, assuming consistent time formatting.
2. Avoid LIKE with Wildcards: Trailing wildcards can sometimes use indexes, but BETWEEN is more predictable.
3. Split Columns for High-Volume Data: If queries frequently filter on hour/minute/second separately, split columns with composite indexes reduce overhead.

By following these steps, you can systematically identify the optimal strategy for your specific workload and data distribution.