> (2) Is there a way to create FTS virtual table that only holds the > fulltext index and not the data itself? (This way I'd able to solve > the problem by creating a separate FTS3 table and hooking triggers on > r/w queries).
Can you exclude text fields from your table, put it into separate FTS table and put rowid from FTS table to the main table? This way you exclude double storage of text information, your data will be linked with text as it is now and you will be able to create indexes on main table as well as perform full-text search in FTS table. The only thing you will miss is indexes on text fields but probably you don't need them when you have FTS... Pavel 2009/12/4 Vladimír Třebický <vladimir.trebi...@gmail.com>: > Hi all, > > I'd like to add a fulltext index to a complex table. The table already > has couple of indeces built on top of it. I created a virtual FTS3 > table with identical schema and inserted all the data there. As one > cannot create indexes on top of virtual tables, most of the queries > the table was originally designed for were slow (full scans). FTS3 > creates couple of regular tables to store the data itself, *_content > being one of them. I tried to build the original indexes on top of the > "*_content" physical table and, indeed, queries executed on the table > were fast. However, executing the same query on the virtual table > remained slow. I looked in fts3.c source code and obviously, > "fulltextBestIndex" function only handles DOCID, FULLTEXT and GENERIC > queries. Looking at "fulltextFilter", GENERIC queries always lead to > full table scan. > > Now, I guess there's currently no way for FTS3 to use generic indexes, > but couple of general questions remain: > > (1) Is it possible to build a fulltext index over specific columns? > (Not all of them?) > > (2) Is there a way to create FTS virtual table that only holds the > fulltext index and not the data itself? (This way I'd able to solve > the problem by creating a separate FTS3 table and hooking triggers on > r/w queries). > > Thanks, > Vladimir. > _______________________________________________ > 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
