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?

EXPLAIN QUERY PLAN <query>

Colonel Thirty Two
– Colonel Thirty Two

2016-02-10 13:10:33 +00:00
Commented Feb 10, 2016 at 13:10 — Colonel Thirty Two
– Colonel Thirty Two, Commented Feb 10, 2016 at 13:10

CL. · Accepted Answer · 2016-02-10 12:59:39Z

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;

answered Feb 10, 2016 at 12:59

CL.

182k18 gold badges241 silver badges282 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Index Optimization for Sqlite3 Database

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related