On 12/2/15, Bart Smissaert <bart.smissaert at gmail.com> wrote: >> and the SQLite query planner sometimes notes that length when considering > data shape > > In what situations does that happen? >
CREATE TABLE ex1(a INTEGER, b VARCHAR(5), c VARCHAR(5000)); CREATE INDEX ex1b ON ex1(b); CREATE INDEX ex1c ON ex1(c); SELECT * FROM ex1 WHERE b=?1 AND c=?2; The query planner is faced with the decision of whether to use the ex1b or ex1c index. Statistics gathered by ANALYZE would normally break this tie, but suppose ANALYZE has not been run, or suppose both indexes are equally selective. In that case, SQLite would choose ex1b since it guesses the keys would be shorter and will compare faster and the fanout will be greater, and hence extb can be searched using fewer CPU cycles. -- D. Richard Hipp drh at sqlite.org
