On Sun, May 6, 2012 at 6:17 AM, Jos Groot Lipman <donts...@home.nl> wrote:
> With fts4aux, the term table would be a perfect candidate to implement an > autocomplete function in our application. > > However a query > SELECT term FROM ft_terms WHERE term LIKE 'descr%' > is quite slow as no 'index' is used (indexes are not possible on virtual > tables). > > With checkin <http://www.sqlite.org/src/info/386701de> > http://www.sqlite.org/src/info/386701de Dan added an optimization for > queries with '=', '<' and '>' in fts4aux so I rewrote my query to > SELECT term FROM ft_terms WHERE term BETWEEN 'descr' AND 'descr' || > CAST(x'FF' AS CHAR) > and indeed: blazingly fast. > > Is there any risk with this (rather ugly) workaround? > This is basically what "term GLOB 'descr*'" does. Did you try using GLOB instead of LIKE. Remember that LIKE is case insensitive whereas GLOB is case sensitive, and fts4aux is case sensitive, so the optimization cannot be applied to LIKE (unless you run PRAGMA case_sensitive_like). > > Is there any chance an optimization gets added for the simple LIKE 'abcd%' > filter? I think it will be a very common usage case for the fts4aux virtual > table > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- D. Richard Hipp d...@sqlite.org _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
