Re: [sqlite] Query on primary key not using primary key?

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

Re: [sqlite] Query on primary key not using primary key?

On Fri, Jun 7, 2013 at 12:56 PM, Brad House 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

Re: [sqlite] Query on primary key not using primary key?

On 06/07/2013 12:46 PM, Simon Slavin wrote: On 7 Jun 2013, at 5:37pm, Brad House wrote: I've modified my code to run an Analyze on startup to work around this, but it obviously takes time to run and slows down startup. I can't answer your question about why this happens

Re: [sqlite] Query on primary key not using primary key?

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

Re: [sqlite] Query on primary key not using primary key?

On 7 Jun 2013, at 5:37pm, Brad House wrote: > I've modified my code to run an Analyze on startup to work around this, > but it obviously takes time to run and slows down startup. I can't answer your question about why this happens in the first place, but I can tell you that

Re: [sqlite] Query on primary key not using primary key?

On Fri, Jun 7, 2013 at 12:37 PM, Brad House 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

[sqlite] Query on primary key not using primary key?

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. I would also think it would assume any index which covers the most where-clause components would be the most efficient