Hello, I'm Maneshwar. I'm building git-lrc, an AI code reviewer that runs on every commit. It is free, unlimited, and source-available on Github. Star Us to help devs discover the project. Do give it a try and share your feedback for improving the product. In the previous part, you saw how SQLite breaks the WHERE clause into terms and uses strict rules to decide whether indexes can be applied. Now we go deeper into specific operators that appear frequently in real queries and how SQLite optimizes them. These operators may look simple at the SQL level, but internally SQLite often rewrites them or applies special strategies to make them efficient. How SQLite Handles the BETWEEN Clause The BETWEEN clause is commonly used for range queries. For example: SELECT * FROM users WHERE age BETWEEN 18 AND 30; SQLite does not treat this as a special standalone operation. Instead, it rewrites it internally into two conditions: age >= 18 AND age <= 30 This transformation introduces two virtual terms. These terms are not explicitly written by the user, but they are used by the optimizer to reason about the query. If both of these conditions can be satisfied using an index, SQLite performs a range scan on that index. It sets: The lower bound as 18 The upper bound as 30 In this case, the original BETWEEN condition is completely eliminated during execution. No additional checks are required for each row because the index guarantees correctness. If the index cannot fully satisfy both conditions, SQLite still uses these virtual terms as hints for optimization. However, the original BETWEEN condition is then evaluated for each row, with the important detail that the expression on the left side is computed only once. How SQLite Optimizes OR Conditions The OR operator is tricky because it often prevents straightforward index usage. Consider this query: SELECT * FROM users WHERE age = 20 OR age = 25 OR age = 30; SQLite first checks whether all OR-connected conditions refer to the same column. If they do, it rewrites the query into an IN clause: SELECT * FROM users WHERE age IN (20, 25, 30); This transformation allows SQLite to use an index efficiently. There are two important rules here: The same column must appear in every OR term The column can appear on either side of the equality operator If an index exists on that column, SQLite uses it just like any other IN query. When OR Cannot Be Rewritten If the OR conditions involve different columns, SQLite cannot rewrite them into an IN clause. Example: SELECT * FROM users WHERE age = 20 OR salary = 50000; In this case, SQLite applies a different strategy. It analyzes each OR term separately, almost as if each one is its own WHERE clause. If each term can use an index, SQLite executes them independently and then combines the results, removing duplicates if necessary. If some terms cannot use indexes, performance degrades. In the worst case, SQLite may fall back to a full table scan. Pattern Matching with LIKE and GLOB The LIKE and GLOB operators are used for pattern matching. Example: SELECT * FROM users WHERE name LIKE 'A%'; or SELECT * FROM users WHERE name GLOB 'A*'; Although these look simple, their optimization depends on several conditions. Case Sensitivity Differences GLOB is always case sensitive. LIKE is more flexible. By default, LIKE is case-insensitive for basic ASCII characters. This means: 'a' LIKE 'A' evaluates to true. You can change this behavior by enabling case-sensitive mode using a pragma or build configuration. However, for non-ASCII characters, comparisons are always case sensitive unless a custom collation is used. When LIKE and GLOB Can Use Indexes SQLite can use indexes for LIKE and GLOB, but only if strict conditions are met. First, the left-hand side must be an indexed column with text affinity. Second, the pattern on the right-hand side must be a string literal or a bound parameter that behaves like a literal. Most importantly, the pattern must not start with a wildcard. Example that can use an index: SELECT * FROM users WHERE name LIKE 'A%'; Example that cannot use an index: SELECT * FROM users WHERE name LIKE '%A'; In the second case, SQLite cannot determine a starting point in the index, so it must scan rows. Additional Conditions There are several other requirements for index usage: The ESCAPE clause must not be used The built-in LIKE or GLOB functions must not be overridden For GLOB, the column must use the default BINARY collation For LIKE: If case-sensitive mode is enabled, BINARY collation is required If case-insensitive mode is used, NOCASE collation is required These constraints ensure that the index ordering matches how comparisons are performed. In the next part, we will look at join table ordering, where SQLite decides the sequence in which tab