On Wed, Aug 5, 2009 at 5: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 :-(
That is strange. I would have thought that a query of the form
SELECT topic FROM topics WHERE topicID=9874;
would use an index on topicID
whereas, to use FTS for search, which you would use only for textual
searches, and, for which, you would have a fts-specific table called,
say 'fts_topics', you would have to do something like
SELECT * FROM topics t JOIN (
SELECT rowid, Snippet(fts_topics, '', '', '...') AS context
FROM fts_topics
WHERE topic MATCH ?
) ft ON t. topicID = ft.rowid
That way you would get the best of both indexed SQL as well full-text searches.
>
> 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
>
--
Puneet Kishor http://www.punkish.org
Carbon Model http://carbonmodel.org
Charter Member, Open Source Geospatial Foundation http://www.osgeo.org
Science Commons Fellow, http://sciencecommons.org/about/whoweare/kishor
Nelson Institute, UW-Madison http://www.nelson.wisc.edu
-----------------------------------------------------------------------
Assertions are politics; backing up assertions with evidence is science
=======================================================================
Sent from Madison, WI, United States
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
