Hi Simon, Thanks for your response. > You need an index, obviously. You can't have an index on a VIRTUAL > table.
Yes, I did try on a regular table (not FTS3) and added the indices I described (one each on Author, Content and Category) and still did not notice any performance improvement. And the db size increased a fair amount. > Could you instead create a TEMPORARY table and read the data into > it ? That can have indices on all the fields you care about. You can > even copy all the entries from the VIRTUAL table into it in one > instruction using the form of INSERT that has a SELECT in it. It's > faster to load the data into the table first, then create the indices. That is a good idea. I will experiment with this. The database is basically read only. So I could create some other tables. > The other aspect of this is to wonder why you're using a VIRTUAL table > in the first place. Why aren't you searching the source data instead > of a virtual table ? The SELECT you described doesn't use the fts3 > feature. Yes, When the project began, I was planning on using full text search. As the project moved forward, I ended up not using it at this point, although future enhancements do call for using full text search. For this iteration, if there is a better way, without using FTS, I could certainly remove it and use a regular table. Thanks. Paul On Sun, Jun 7, 2009 at 11:00 PM, Simon Slavin <slav...@hearsay.demon.co.uk>wrote: > > On 8 Jun 2009, at 4:45am, Paul Perry wrote: > > > The latest content I have added, has over 31,000 records. A basic > > select, > > such as: > > > > Select Content from BookContent where DocumentID = 10; > > takes nearly 15 seconds. Are there any ways to optimize this? > > You need an index, obviously. You can't have an index on a VIRTUAL > table. > > Could you instead create a TEMPORARY table and read the data into > it ? That can have indices on all the fields you care about. You can > even copy all the entries from the VIRTUAL table into it in one > instruction using the form of INSERT that has a SELECT in it. It's > faster to load the data into the table first, then create the indices. > > The other aspect of this is to wonder why you're using a VIRTUAL table > in the first place. Why aren't you searching the source data instead > of a virtual table ? The SELECT you described doesn't use the fts3 > feature. > > Simon. > _______________________________________________ > 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
