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