fts has an implicit index on the docid (aka rowid), and a fulltext index on the terms in the columns. All columns are treated as TEXT, you can say INTEGER or VARCHAR, but they are TEXT.
The end of this page: http://code.google.com/apis/gears/api_database.html has an example of how you might construct two tables which can be joined to work like I believe you want things to work. Basically, you have an fts-only table, then another table which contains the things you wish to index in more traditional SQL ways. -scott On Wed, Aug 5, 2009 at 3:37 PM, Lukas Haase<lukasha...@gmx.at> wrote: > Hi list, > > I have a huge problem: A database with 20000 HTML fragements should > contain a fulltext index. For that reason I put all data into a virtual > table: > > CREATE VIRTUAL TABLE topics USING fts3( > topicID INTEGER, > topic_title VARCHAR(200) COLLATE NOCASE, > topic TEXT, > TOKENIZE simple); > > topic contains the HTML fragments, topic_title the title and topicID is > needed for locating a specific entry. Well, and that's actually the > problem... Before (i.e. without FTS) I did: > > SELECT topic FROM topics WHERE topicID=9874; > > which was quite fast. Now this is very, very slow (a few seconds!). I > guess this is because topicID is not a primary key any more and no index > is defined. So I wanted to create an index but I got the error in the > subject. Really big problem :-( But I really need a way to *quickly* > locate an entry by its ID. I do NOT want to store the data twice :-( > > What I am doing wrong? > > Best regards, > Luke > > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
