Thanks, that was very useful. I didn't realize that table values could be obtained from the index. I suppose it makes sense when you think about it.
So, basically it is best to make one large index (apart from the primary integer key?) that includes all fields that could be in a WHERE clause or a JOIN or a GROUP BY or a HAVING or an ORDER BY? RBS -----Original Message----- From: Igor Tandetnik [mailto:[EMAIL PROTECTED] Sent: 04 August 2007 15:18 To: SQLite Subject: [sqlite] Re: How does SQLite choose the index? RB Smissaert <[EMAIL PROTECTED]> wrote: > I get this query plan (explain query plan): > > order from detail > ---------------------------------------------------- > 0 0 TABLE ENTRY AS E WITH INDEX > IDX16$ENTRY$READ_CODE$ADDED_DATE$START_DATE$PROBLEM_ID > > My question is why doesn't it pick the index: > IDX3$ENTRY$READ_CODE > > Not sure, but I would think that is more effective. It's exactly the same in terms of efficiency. In fact, it is completely pointless to have two indexes where the column list of one is a strict prefix of the column list of another. The latter can be used, equally efficiently, everywhere the former can be used. In some cases the latter may even be more efficient. Consider: create table t (a text, b text); create index ta on t(a); create index tab on t(a, b); select a, b from t where a='xyz'; If SQLite chooses to use index ta, then it needs to perform a lookup in the table (by rowid) to retrieve the value of b. But if it uses index tab, then the value of b is stored in the index, and the table itself doesn't need to be consulted at all. So fewer pages to read from disk. Igor Tandetnik ---------------------------------------------------------------------------- - To unsubscribe, send email to [EMAIL PROTECTED] ---------------------------------------------------------------------------- - ----------------------------------------------------------------------------- To unsubscribe, send email to [EMAIL PROTECTED] -----------------------------------------------------------------------------
