"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] -----------------------------------------------------------------------------
