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 [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
