On 21/08/2009 1:29 PM, pierr wrote:
>
> Simon Slavin-2 wrote:
>>
>> On 21 Aug 2009, at 3:26am, pierr wrote:
>>
>>> I did not know the sequence in defining the field matters. This is
>>> what I should have done.
>> Sorry, I should have explained better. You were right: there is no
>> difference. I was just rearranging the fields in the classic way:
>> with the primary key column as the first column. It helps me think
>> about how the database works. You did nothing wrong.
>>
> Hi Simon,
> It do make a difference.
> With this schema,
> CREATE TABLE IF NOT EXISTS tblIndex(
> frame_type INTEGER,
> pts VARCHAR(5)
> ts_start INTEGER PRIMARY KEY,
> ts_end INTEGER,
> )
> There will be a rowid field in the database ; and there is a
> sqlite_autoindex_tblIndex_1 build on ts_start automatically ; so 1,800,000
> records (16bytes each) takes 62M .
Please don't type from memory -- what you have above has TWO syntax
errors, and doesn't (with those random comma placements fixed) produce
the result that you say -- and do read my previous message.
Here is an (annotated) copy/paste of an actual session:
sqlite> CREATE TABLE IF NOT EXISTS tblIndex(
...> frame_type INTEGER,
...> pts VARCHAR(5) <<<=== missing comma
...> ts_start INTEGER PRIMARY KEY,
...> ts_end INTEGER,
...> )
...> ;
SQL error: near "ts_start": syntax error
sqlite> CREATE TABLE IF NOT EXISTS tblIndex(
...> frame_type INTEGER,
...> pts VARCHAR(5),
...> ts_start INTEGER PRIMARY KEY,
...> ts_end INTEGER, <<<=== superflous comma
...> );
SQL error: near ")": syntax error
sqlite> CREATE TABLE IF NOT EXISTS tblIndex(
...> frame_type INTEGER,
...> pts VARCHAR(5),
...> ts_start INTEGER PRIMARY KEY,
...> ts_end INTEGER
...> );
sqlite> select * from sqlite_master;
table|tblIndex|tblIndex|2|CREATE TABLE tblIndex(
frame_type INTEGER,
pts VARCHAR(5),
ts_start INTEGER PRIMARY KEY,
ts_end INTEGER
) <<<=== no index !!
sqlite> drop table tblIndex;
sqlite> CREATE TABLE IF NOT EXISTS tblIndex(
...> frame_type INTEGER,
...> pts VARCHAR(5),
...> ts_start INT PRIMARY KEY, <<<=== using your original INT
instead of Simon's INTEGER
...> ts_end INTEGER
...> );
sqlite> select * from sqlite_master;
table|tblIndex|tblIndex|2|CREATE TABLE tblIndex(
frame_type INTEGER,
pts VARCHAR(5),
ts_start INT PRIMARY KEY,
ts_end INTEGER
)
index|sqlite_autoindex_tblIndex_1|tblIndex|3| <<<=== index!!
sqlite>
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
