With the relatively new prefix option FTS4 appears to be slightly faster now...could be within the error spread though.
I had to upgrade to 3.7.8 -- not sure when that prefix came in but it wasn't in 3.7.5. Might be nice if the docs mention in what version something shows up. sqlite> create virtual table ftext using fts4(t text,prefix="1"); sqlite> begin; sqlite> .read data2.sql sqlite> commit; sqlite> .timer on sqlite> select count(*) from ftext where t match 'a*'; 38462 CPU Time: user 0.004999 sys 0.000000 sqlite> select count(*) from ftext where t match 'b*'; 38462 CPU Time: user 0.003999 sys 0.000999 sqlite> select count(*) from ftext where t match 'c*'; 38462 CPU Time: user 0.004999 sys 0.000000 sqlite> select count(*) from ftext where t match 'd*'; 38462 Using the table method sqlite> .timer on sqlite> select count(*) from text where left=98; 38462 CPU Time: user 0.005999 sys 0.000000 sqlite> select count(*) from text where left=99; 38462 CPU Time: user 0.004999 sys 0.000000 sqlite> select count(*) from text where left=97; 38462 CPU Time: user 0.004999 sys 0.000000 sqlite> select count(*) from text where left=100; 38462 CPU Time: user 0.004999 sys 0.000000 Although the database is quite a bit bigger. Table method -- 4.24GB FTS3 -- 4.92GB FTS4 -- 5.67GB 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 3:58 PM To: General Discussion of SQLite Database Subject: EXT :Re: [sqlite] Can pre-sorted data help? On Oct 9, 2011, at 10:46 PM, Black, Michael (IS) wrote: > create virtual table ftext using fts3(t text); Try this instead: create virtual table ftext using fts4(t text, prefix="1") 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
