> 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.
Ok, I'll try to be as specific as possible. The main table I have is (the real version has much more fields, but it isn't important for our example): CREATE TABLE Songs ( ID INTEGER PRIMARY KEY AUTOINCREMENT, Artist TEXT COLLATE IUNICODE, Album TEXT COLLATE IUNICODE, SongTitle TEXT COLLATE IUNICODE, Path TEXT COLLATE IUNICODE, Year INTEGER, Bitrate INTEGER) This table can have even >100k records, even close to million and is mostly accessed by SELECTing all fields of some records, i.e.: SELECT * FROM Songs WHERE {something} In order to use FTS3, I could take all the text fields from Songs table and move them to a FTS3 table: CREATE TABLE SongsBase ( ID INTEGER PRIMARY KEY AUTOINCREMENT, Year INTEGER, Bitrate INTEGER) CREATE VIRTUAL TABLE SongsText USING FTS3(TOKENIZE mm, Artist, Album, SongTitle, Path) This way I would lose my custom collation (IUNICODE), which would be quite a problem, particularly for Path field (and if you're asking, yes, I'd like to include Path in the full-text index). Another problem is that joined SELECT on SongsBase and SongsText is slower than SELECT on the original Songs table. So, the only solution using FTS3 seems to be to use the original Songs table and add SongsText table, automatically updated by triggers like: CREATE TRIGGER update_songs UPDATE OF Artist,Album,SongTitle,Path ON Songs BEGIN UPDATE SongsText SET Artist=new.Artist, Album=new.Album, SongTitle=new.Title, Path=new.Path WHERE rowid=new.id; END; This solution probably isn't bad, but according to my knowledge of FTS3, it unnecessarily occupies some DB space (all text fields are actually stored twice, once in Songs and once in SongsText). Any ideas or recommedations? Thanks, Jiri _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users