I have three types columns in my table, which I want to move to a FTS4 table, but I'm not sure whether that would make sense.
1.) This column contains hash-values. I always need to lookup 100% exact matches. Normally you would store them with 'TEXT INDEX' in a standard table. Would there be any advantage moving this column to FTS? Or would it perform worse than 'TEXT INDEX'? 2.) This column also contains hash-values, but I always need to look them up by prefix (LIKE 'start%'), never by exact match. And the size of this prefix is unknown in advantage, so I can't use FTS4 optimization for fixed-size prefix searches. Is there any advantage over a traditional LIKE query? Since I suspect FTS to only index full words, which would mean no advantage. 3.) This column contains a list of short tags seperated by spaces. For example: 'v45 s12 h65', which I currently look up using 'LIKE '%tag%', to search for a certain combination of tags. In a good database-design, these tags would be stored in a second table, referencing the 'rowid' of the first table. But every row can have many tags, and storing them in normalized form, would make the INSERTS very slow, because I have to insert an extra row for each tag, instead of just one row. Since my app does much more inserts than actual selects, I choosed to store them into a single column. Would there be any advantages storing these tags into a FTS column, so I can do quick lookups? One concern I have is that they're not unique enough (just like stopwords), eventually bloating the index. Or is the only right option to create a second table with a TEXT INDEX column on these tags? _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
