Good day, Randolph. While I haven't has the time to review the source, like Max has, I can attest from personal experience that SQLite FTS3 (or FTS4) is a possible and viable solution.
My utilization of SQLite is as a programming API DB for RIMs Blackberry OS' (as personal endeavor, as I am not affiliated with RIM in any manner). It has all versions of the OS' from 4.2.1 thru the latest 6.0.0. The DB contains ~142k records with 9 or 10 fields, including URLs. I get sub-second FTS searches - from Search Term submission to readable/clickable displayed results. The majority of the delay, I am confident, is in my host language as it is interpreted, not optimized/compiled... FTS scared the heck out of me at first, until I broke down, took the time to read the docs and realized that it was not painful at all. In the end, the time I wasted thinking about reading the docs was much more time than it actually took me to read same, and make the required code changes to implement FTS. (/me says 'Bah' to thinking things through in the future... ;-) In the interests of full disclosure, however, it was not entirely pain-free. When combined with my lack of OOP skills, the strong desire/need to have an external relevancy sorting function required me to implement callbacks in my host language, in order to arrive at a custom implementation of the ranking function shown in the last portions of the FTS3/FTS4 docs: http://www.sqlite.org/fts3.html#appendix_a. If you do not need to rank your search results based on 'relevancy', then you may not have to toil with callbacks (scalar or otherwise.). In the end, it is only my ignorance that cause any of the pain. If you are skilled in the dev environment you are coding in, then you'll likely have absolutely no issues. I echo Max's assertions in suggesting that you research and seriously consider migrating over to FTS. I suspect that you will be more than pleased. -Tod Wulff p.s. While drafting this, I had a bit of an epiphany regarding further enhancing the speed of FTS (not that it is required), via the removal of all stop-words and sorting (<-- that is not uncommon, what is, is my understanding of how to do this -->) yet having the capability to pull in readable chunks of text out of the docs. This will come at the cost of some redundant data in the tables, but... It will be good to implement, allowing me to know if my suspicions are founded in truth, and just how much of an improvement there will really be (it is probably not significant, I suspect). Take Care. L8R. -t -----Original Message----- From: [email protected] [mailto:[email protected]] On Behalf Of Max Vlasov Sent: Sunday, December 26, 2010 07:27 AM To: General Discussion of SQLite Database Subject: Re: [sqlite] Searchtime in SQL On Sun, Dec 26, 2010 at 12:51 PM, Randolph Dohm <[email protected]>wrote: > Hello > > we develop a bookmark database for urls, http://arado.sf.net and used > SQLite with Qt. > With 7000 database entries of urls the search for a keyword takes up > to 15 seconds to respond. > That is quite a long time, why is SQL so slow? is there a way to > improve the speed besides to switch to another database or to develop > an own TXT file on the harddisk? > Randolph, I looked at your schema in the sources and a query that formed in SearchAny method. Maybe there's something you can do with this to improve the speed, but believe me, the best you can do is to move toward full-text search (compile your qt sqlite adapter fts3/fts4 enabled). Just imagine a dedicated user of your application in ten years, he never deleted urls, only added and every month he or she feels that the search becomes slower and slower. With full-text search you have to do something very special to make it really slow. Max Vlasov _______________________________________________ sqlite-users mailing list [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
