On 2 May 2013, at 2:57pm, Jan Slodicka <j...@resco.net> wrote: > LIKE is used at the moment, but it has its perf limits as well. To prove it, > here is a desktop benchmark: > > Desktop: W7, x64, Intel i5, 2.4 GHz > 116 MB email table containing 10000 html-formatted emails. > SELECT ... LIKE...'%xxx%'... command took 35.5 sec. (Producing 9 results) > SELECT ... MATCH ... 'xxx*' command took 0.00 sec, i.e. somewhere between > 0-7 msec. (By chance producing the same results) > > The funny thing was that the creation of the email FTS index took 22-23 sec, > i.e. was faster than a single LIKE statement.
Thank you for this interesting and surprising information. The resource and time cost of doing any writing on a mobile device is usually far greater than simply doing processing in memory. It appears that this is not true in your case. Hmm. > My plan is to implement FTS search as an optional feature: > - Several FTS indexes grouped into multi-indexes (Example: people names may > be in one of 3 tables: accounts, contacts, leads. These tables would > contribute to PeopleSearch activity.) > - FTS index is built on demand (when the user tries to use it) > - The build procedure must be cancellable > - Once built, the index will be maintained using triggers (for small data > changes) > - For large data changes (happens during synchronization when the server > sends a lot of data) the FTS index is dropped > > Do you see any risks with this scheme? It appears that you are aware of the issues and have thought this through. Sorry for troubling you. Simon. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
