On Sat, Sep 11, 2010 at 7:52 AM, Max Vlasov <[email protected]> wrote:
> Cory, thanks, just wondering, is the nature of the index internals is a part > of SQL standard or just a common sense. For example, I can imagine real > example when the space used is not a big problem so index could contain > other non-indexed fields just for benefits of returning them without > necessity to query the main table. Also (unlikely really used, but > theoretically possible) minimalistic approach to space when only rowids (or > equivalent) is saved and every operation including comparision operation > requires querying the main table by rowid. It seems that in the first case > the benefits of the index for faster loading will be completely lost. > > Max First, I'd like to point out that if you fiddle around with EXPLAIN, you will find that, interestingly enough, creating an index internally behaves a lot like if you placed insert/update/delete triggers on your table -- but the index requires a LOT less code (in fact, a single instruction). Second, on the subject of indexes: Indexes have two properties that make them beneficial in two different ways: 1. They are sorted, so any WHERE clause that matches the first N columns can be matched very effectively. 2. They contain only a subset of the columns in the main table, so they are smaller -- so reading through the entire index is faster than reading through the entire table. Now, my tests indicate that SQLite's optimizer is *not* clever enough to account for #2: CREATE TABLE Foo (value integer primary key, insertdate text not null, name text not null); CREATE INDEX date_IX on Foo (insertdate); sqlite> explain query plan select insertdate from foo; orde from deta ---- ------------- ---- 0 0 TABLE foo It would be an interesting attempt to try efficiently patching SQLite to recognize this situation and read data directly out of the index. -- -- Stevie-O Real programmers use COPY CON PROGRAM.EXE _______________________________________________ sqlite-users mailing list [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
