Ok, thank you for clarifying those points to me. At the moment I'm struggling to understand why running a
SELECT ROWID FROM TableA WHERE colA='a' Takes almost twice as long as SELECT ROWID FROM TableA WHERE colA='b' Running on the same table with the same data with colA indexed. Unfortunately, the real queries are not as simple, although still the same between each other, just different data. I'm still in the process of singleing out the query that is different (using EXPLAIN), so as soon as I know which it is I'll be able to study it and see how can I change it. Regards, Dennis > -----Original Message----- > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] > Sent: Wednesday, March 28, 2007 10:59 AM > To: sqlite-users@sqlite.org > Subject: Re: [sqlite] Questions on views > > "Dennis Volodomanov" <[EMAIL PROTECTED]> wrote: > > 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, ...) > > Yes. SQLite has to scan through all the tables in order > until it finds the one you asked for. So put all of your > large strings and big BLOBs at the end so that you won't have > scan past them to find that boolean you were looking for. > > > > > And if yes, would it still matter if colZ was indexed? > > Not in this case, no. > > > > > 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) > > IndexA is not needed. IndexC does everything that IndexA can do. > > > > > So that would cover all possible lookups such as: > > > > SELECT EXISTS (SELECT ROWID FROM TableA WHERE colA='a' AND colC='c') > > This would use IndexC on the colA='a' constraint only. But > because all the information it needs is contained in indexC, > it would never have to reference TableA. > > > SELECT EXISTS (SELECT ROWID FROM TableA WHERE colB='b' AND colC='c') > > This will use IndexB on both colB='b' and colC='c'. And > tableA is never consulted. > > -- > D. Richard Hipp <[EMAIL PROTECTED]> > > > -------------------------------------------------------------- > --------------- > To unsubscribe, send email to [EMAIL PROTECTED] > -------------------------------------------------------------- > --------------- > > ----------------------------------------------------------------------------- To unsubscribe, send email to [EMAIL PROTECTED] -----------------------------------------------------------------------------
