Re: Live Search

On Tue, Dec 1, 2009 at 00:33, Stewart Lord <stewey@ambitious.ca> wrote: > On Mon, 30 Nov 2009 11:05:14 +0100, Hannes Magnusson > <hannes.magnusson@gmail.com> wrote: >> >> As far as I know then the database created by sqlite2 is incompatible >> with sqlite3, so PhD probably will have to create both variants :( > > > Hi Hannes, > > I am running into a couple of problems with the sqlite search. I was hoping > you could help. > > 1. Scoring > The quickref.php code has a neat trick for scoring results based on where > the keyword appears in the function/class name. I don't see any obvious way > > to get the same effect through an sqlite query given it's limited string > functions. > > For queries that don't directly match, the quickref.php script uses the > similar_text() function to calculate the similarity between the keyword > and the function/class name. The similar_text function produces a > similarity percentage which we can use to rank results by 'relevance' and > weed out poor matches. I don't see any clear way to achieve the same > effect with metaphone. Actually, the metaphone matching is presently > disabled in manual-lookup.inc (line 135) with a "FIXME" citing too many > false positives. Storing the soundex and metaphone info in the database should help. Then we can match against those and full text search. Iterating over the results and ranking them even further with similar_text. > 2. Performance > On my laptop, the sqlite script is taking 90ms to produce the list of all > functions/classes that match my query (e.g. 'sub') for a given language > (e.g. 'en'). The SQL looks like this: > >  SELECT name FROM fs WHERE lang='en' AND keywords LIKE '%sub%'; This isn't the database created by PhD, this is the one created by magical systems/ script[1]. It is both inaccurate and doesn't contain all the info we'd like to have. The database created by PhD is less then 3mb (currently only English though...). sdesc=Short description (the refname, usually a function name, i.e. "strpos") ldesc=Long description (usually the refpurpose, i.e. "Find position of first occurrence of a string") filename=The (rendered/output) filename containing this info SELECT filename, ldesc, sdesc FROM ids WHERE sdesc LIKE '%sub%'; CPU Time: user 0.020001 sys 0.012000 Gives me 52 hits for methods and functions. SELECT filename, ldesc, sdesc FROM ids WHERE ldesc LIKE '%sub%' OR sdesc LIKE '%sub%'; CPU Time: user 0.024002 sys 0.012001 Gives me 99 results as it searches the purpose/description also. I can send you a generated copy of the PhD database if you want? Or you could create it yourself by rendering the docs :) -Hannes [1] http://svn.php.net/viewvc/systems/trunk/gen-phpweb-sqlite-db.php?revision=284247&view=markup