If I had the time right now I'd write a benchmark.... I find it really hard to believe that for prefix queries that FTS is going to be faster than a seperate table. Your assumption is that it is. Especially for the first character situation. And it's selectivity is one-to-one for the query he posed. A=A, B=B, etc.....
Has anybody bothered to benchmark this? The table I described takes up 1 byte per record for the text...so you can fit more in cache and memory. FTS is mainly for searching the entire record....I think that's where it shines....being able to prefix query is just a natural feature...not necessarily the most optimal way of doing it. And sorting data can help speed as it reduces fragmentation of the data that has to be retrieved so caching can work better. Michael D. Black Senior Scientist NG Information Systems Advanced Analytics Directorate From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on behalf of Petite Abeille [petite.abei...@gmail.com] Sent: Sunday, October 09, 2011 9:08 AM To: General Discussion of SQLite Database Subject: EXT :Re: [sqlite] Can pre-sorted data help? On Oct 9, 2011, at 1:03 PM, Black, Michael (IS) wrote: > For your example create a separate table with just the first letter and build > an index on it. This is most likely pointless as the selectivity of such index is going to be very low. Plus not point in reinventing a square wheel, instead let FTS handle this :) FTS4 supports prefix queries: http://www.sqlite.org/fts3.html#section_6_2 _______________________________________________ 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
