I would expect all queries which specify the primary key components
in the WHERE clause to use the Primary Key in the query plan, regardless
of if ANALYZE has been run or not.
SQLite examines many different strategies for evaluating each query. For
each strategy it tries to estimate the total run-time. It then selects the
strategy that gives the least run-time. Whether or not the PRIMARY KEY is
used as part of that strategy is not a consideration.
ANALYZE does not change this. The purpose of ANALYZE is merely to provide
additional information to help SQLite give a better estimate of the
run-time for each of the query strategies under consideration.
I guess I just don't understand how it would come up with a run-time strategy
to NOT use a primary key (or any unique index) when the WHERE clause _exactly_
matches such an index. It also seemed to 'guess' that there'd be 2 result
records without ANALYZE data and thus somehow chose a non-unique index utilizing
fewer columns over a unique index ...
The performance penalty is huge in my example, it's the difference of ~4s vs
~0.005s.
Is this really not considered an issue/bug?
Thanks.
-Brad
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
