On Sat, Sep 11, 2010 at 2:24 PM, Max Vlasov <[email protected]> wrote:
> On Sat, Sep 11, 2010 at 8:38 PM, Stephen Oberholtzer <
> [email protected]> wrote:
>
>> 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.
>>
>>
> Stephen, are you telling that is' smaller in any situation? When I mentioned
> the trigger in case of fast reading of rowid/id, I thought that in this case
> there can be a separated table with sing field id (rowid) that should change
> its contents synchronously to the main table that contains all data. I
> suppose in this case the two variants (index vs trigger) is on par in terms
> of the size or am I wrong?
>
> Max
Underneath the surface, an index is just a mini-table that contains
the indexed columns, plus the rowid, and is stored in sort order.
An index will always contain the indexed columns, plus the rowid.
Since their is no way to have MORE columns in an index than in the
table itself, there is no way for an index to be bigger than its
table. The worst case is when the index has every single column in the
table, in which case the index is exactly the same size as the table
(because it contains the exact same data, just in a different order).
In fact, the table itself is basically an index with 'rowid' as the
first column.
When you generate an insert/update/delete statement, SQLite
automatically generates code to maintain the index (updates are
handled by deleting + reinserting):
CREATE TABLE Foo (value integer primary key, insertdate text not null,
name text not null);
CREATE INDEX date_IX on Foo (insertdate);
sqlite> explain insert into Foo (insertdate, name) values ('20100911', 'Steve');
addr opcode p1 p2 p3 p4 p5 comment
---- ------------- ---- ---- ---- ------------- -- -------------
0 Trace 0 0 0 00
1 Goto 0 19 0 00
2 OpenWrite 0 2 0 3 00
3 OpenWrite 1 3 0 keyinfo(1,BINARY) 00
4 NewRowid 0 3 0 00
5 Null 0 4 0 00
6 String8 0 5 0 20100911 00
7 String8 0 6 0 Steve 00
8 HaltIfNull 19 2 5 Foo.insertdate may not be NULL
00
9 HaltIfNull 19 2 6 Foo.name may not be NULL 00
10 SCopy 5 7 0 00
11 SCopy 3 8 0 00
12 MakeRecord 7 2 1 ab 00
13 IdxInsert 1 1 0 10
14 MakeRecord 4 3 9 daa 00
15 Insert 0 9 3 Foo 1b
16 Close 0 0 0 00
17 Close 1 0 0 00
18 Halt 0 0 0 00
19 Transaction 0 1 0 00
20 VerifyCookie 0 4 0 00
21 TableLock 0 2 1 Foo 00
22 Goto 0 2 0 00
Step #12 builds the index record and step #13 performs an insert into
the index. You may note that #12 builds a record with 2 columns, when
the index definition only has 1. That's because every index implicitly
includes the rowid. If I were to add additional indexes to Foo, there
would be additional (SCopy + MakeRecord + IdxInsert) instructions for
each one.
--
-- 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
