Again, you've given a relatively broad description of what you're trying to do. I could make up a bunch of stuff and answer my own question, but you'd probably rather than I considered the problem _you_ are having. It sounds like you've actually committed code to do this - post the SQL CREATE statements, a description of the data (maybe a few varied examples, presented as INSERT statements, and a description of the size of your data), then a SELECT statement which does the join you want to do (and some indication of how slow you're seeing it being, and how fast the alternative is).
You earlier suggested that you were doing a join to get all the data in both tables. In the interests of generating an additional data point, you might try comparing the FTS version of the split with a regular table, and see if the join's performance is comparable. Rather than having a regular table with all columns and an fts table with a subset, you could also consider having an fts table with all columns and a regular table with a subset. It's hard to be more specific, though, because you haven't really put up specifics on your end. -scott On Tue, Jul 22, 2008 at 6:33 AM, Jiri Hajek <[EMAIL PROTECTED]> wrote: >> It's hard to say - you might want to describe your tables and the join >> by simply including the SQL itself, rather than a loose description of >> it. It may be that there's a simple change which will clear things >> up. > > Ok, what exactly I mean is a table containing tracks in an audio library, > i.e.: > > Table Tracks: > ID INT > Title TEXT > Artist TEXT > Album TEXT > Length INT > Bitrate INT > ... (and much more) > > The table can be split in two parts, where TEXT fields would be indexed by > FTS, but I'd like to avoid it, due to several reasons, particularly because > of performance. > >> My question to SQLite experts is whether there is any solution to this >> choice between speed and DB size. Preferrably I'd need to specify that > just >> some fields of an ordinary table participate in FTS3 index, but that >> obviously isn't possible, at least not yet. Are there any plans for this? > >> No plans at this time. The goal of the current functionality >> breakdown was to keep SQLite core from having to understand too much >> about FTS, and FTS from having to understand too much SQL. > > From my point of view, it would be best to separate FTS index data from the > actual text data (I think that the idea was already mentioned in another > thread here). It would have several advantages and interesting points. That > said, I haven't studied the FTS implementation and don't have any idea > whether this is technically possible with reasonable effort. > > Btw, whan I mean could be implemented like this: > > CREATE VIRTUAL INDEX ind USING fts4(TOKENIZE simple) ON Tracks(title, > artist, album); > > Jiri > > _______________________________________________ > sqlite-users mailing list > [email protected] > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > _______________________________________________ sqlite-users mailing list [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
