Thank you for the reply. > > One more question that I was pondering upon - does the > order of rows > > and their placement in the table matter at all when using indexes > > (provided indexes are used correctly)? > > I don't really understand the question...
What I meant was - if I have a table like: CREATE TABLE TableA (colA, colB, colC, ..., colZ) And I do a: SELECT ROWID FROM TableA WHERE colZ='z' Would it be faster if I had instead: CREATE TABLE Table (colA, colZ, colB, colC, ...) And if yes, would it still matter if colZ was indexed? > > CREATE INDEX indexA on tableA (columnA, columnB, columnC) SELECT > > EXISTS (SELECT ROWID from tableA WHERE columnA='a' AND > > columnB='b') > > > > The indexA index will be used to resolve the query above, and > nothing else. The tableA table is never consulted. > > To resolve the query above, SQLite does a binary search > through indexA looking for an entry that matches columnA and columnB. > It then returns 1 or 0 on success or failure. tableA does > not come into plan. Would it make sense (if I understood correctly) to create several indexes with different column combinations, such as: CREATE INDEX IndexA ON TableA (colA, colB) CREATE INDEX IndexB ON TableA (colB, colC) CREATE INDEX IndexC ON TableA (colA, colB, colC) So that would cover all possible lookups such as: SELECT EXISTS (SELECT ROWID FROM TableA WHERE colA='a' AND colC='c') SELECT EXISTS (SELECT ROWID FROM TableA WHERE colB='b' AND colC='c') Or is that redundant and wouldn't work properly (size on disk and insert/delete speed doesn't matter to me)? Thank you! Dennis > -----Original Message----- > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] > Sent: Wednesday, March 28, 2007 10:31 AM > To: sqlite-users@sqlite.org > Subject: Re: [sqlite] Questions on views > > "Dennis Volodomanov" <[EMAIL PROTECTED]> wrote: > > One more question that I was pondering upon - does the > order of rows > > and their placement in the table matter at all when using indexes > > (provided indexes are used correctly)? > > I don't really understand the question... > > > > > Oh, and another :) Would such a statement use both indexes > or just one? > > > > CREATE INDEX indexA on tableA (columnA, columnB, columnC) SELECT > > EXISTS (SELECT ROWID from tableA WHERE columnA='a' AND > > columnB='b') > > > > The indexA index will be used to resolve the query above, and > nothing else. The tableA table is never consulted. > > To resolve the query above, SQLite does a binary search > through indexA looking for an entry that matches columnA and columnB. > It then returns 1 or 0 on success or failure. tableA does > not come into plan. > > -- > D. Richard Hipp <[EMAIL PROTECTED]> > > > -------------------------------------------------------------- > --------------- > To unsubscribe, send email to [EMAIL PROTECTED] > -------------------------------------------------------------- > --------------- > > ----------------------------------------------------------------------------- To unsubscribe, send email to [EMAIL PROTECTED] -----------------------------------------------------------------------------
