John Machin wrote: > > 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> > > After reading your previous message , now I understand the difference between "INT PRIMARY KEY" and "INTEGER PRIMARY KEY" which I did not notice before. And sorry for "typing from memory":)
-- View this message in context: http://www.nabble.com/suggestion-on-the-database-design-tp25062134p25074149.html Sent from the SQLite mailing list archive at Nabble.com. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
