On Fri, Jun 7, 2013 at 12:56 PM, Brad House <b...@monetra.com> wrote:
> 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? > Without additional information, SQLite guesses that the data_idx index will narrow down the search to about 7 entries in the table. This is, of course, a guess, but it is a reasonable guess for most indices. The primary key, even though it is unique, has an IN clause with 50 entries, it SQLite guesses it will narrow the search down to 50 entries. SQLite picks the index that leads to the least amount of searching: 7 entries versus 50. In your case, I'm guessing that data_idx is really not a very good index and might ought to be dropped for doing little more than taking up space. What does the sqlite_stat1 entry for data_idx say? -- D. Richard Hipp d...@sqlite.org _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
