Index Optimization for Sqlite3 Database

Question

I have a SQLite table t1 with three columns, c1, c2 and c3 with a composite index on (c1, c2). I want to run the following query:

Select max(c2) from t1 where c1=Value and c3 not like "abcd%".

Now, it can use the composite index to filter only those records with c1 matching the Value and apply where condition for c3 then pick the record with max(c2).

Or it can use the full composite index to filter all record with c1 matching the Value and walk c2 in reverse (descending order) and then compare the where condition.

The second approach will be faster in my case.

The question is how does SQLLite optimize this query: First one or Second one?

Answer 1

SQLite has a MIN/MAX optimization, but only for very simple queries.

For queries with a WHERE clause, the optimizer does not actually know how the MAX() function behaves, so it is treated like any other aggregate function, i.e., the function must be called with every value. Therefore, only the first column of the index will be used.

To be able to use both columns, rewrite the query to use c2 for sorting:

select c2
from t1
where c1 = Value
  and c3 not like 'abcd%'
order by c2 desc
limit 1;