> -----Original Message----- > From: Bertrand Mansion [mailto:[EMAIL PROTECTED] > Sent: 16 January 2004 19:18 > To: [EMAIL PROTECTED]; [EMAIL PROTECTED] > Subject: Re: [sqlite] Full text search implementation > > > <[EMAIL PROTECTED]> wrote : > > > > > > >> -----Original Message----- > >> From: David Morel [mailto:[EMAIL PROTECTED] > >> Sent: 16 January 2004 17:32 > >> To: Brad Campbell > >> Cc: George Ionescu; [EMAIL PROTECTED] > >> Subject: Re: [sqlite] Full text search implementation > >> > >> > >>> My regex patch should do that > >>> > >>> SELECT * FROM Categories WHERE CategoryDescription RLIKE > >> 'Beverages" and CategoryDescription NOT > >>> RLIKE 'Whiskey'; > >>> > >> > >> In such a simple string matching I suspect a regex search is totally > >> overkill... that's ok for a db containing 1000 rows, but try it on > >> 700,000 rows (390Mb) like the one i have here ;-) > >> > > I don't think that your LIKE version will perform much better - SQLite > > doesn't use indexes when doing LIKE comparisons. > > > >>> --------------------------------------------------------------------- > >>> To unsubscribe, e-mail: [EMAIL PROTECTED] > >>> For additional commands, e-mail: [EMAIL PROTECTED] > >> -- > >> *********************************************** > >> [EMAIL PROTECTED] > >> OpenPGP public key: http://www.amakuru.net/dmorel.asc > >> 28192ef126bc871757cb7d97f4a44536 > >> > >> > >> > > > > Using LIKE as a means of doing a full text search is virtually > useless in > > the real world of text retrieval. The query take no account of context, > > which is essential when dealing with intelligent text queries. > > A full-on full text engine (BASIS, BRS etc) has to maintain a set of > > meta-data for each text column that can be searched i.e. > > > > When data is added to a text column, the text must be parsed to > split it up > > into searchable words using a break character list. > > These words must then be reduced to their searchable stem > (pluralisation, > > inflexions, Porter stemming etc) and insignificant words ('a', > 'and', 'the' > > etc (stop words)) removed. > > The words are then added to the column index - the posting in the index > > contains the row ID, the start character position of the word and the > > original length of the word. It may also contain grammatical > context info > > such as the sentence/paragraph number. > > At this point, some systems may also add into the index other > variants of > > the words (common mispellings, morphs etc) to improve recall. > > > > Now, when you do a search on that column, the system has to > parse your query > > terms, stem them and weed out stop words in the same way as > when data was > > added. It then looks up the words in the column index and collates the > > proximity of the words. > > There's not normally much point in searching for 'SQLite' and > 'document' if > > you can't tell the system to find them with the same sentence, > paragraph, or > > adjacent. > > > > As you can see, a proper full text search engine is > considerably more work > > than it first looks. Add onto this all the complexities of > applying this to > > different languages and you have a pretty major coding effort > on your hands. > > > > I have a working prototype of such a beast using SQLite that I'd be > > interested in sharing the devlopment of, if anyone is interested? > > I agree. You just forgot about the scoring algorithm, a full text query > should also be able to return a score. > > IMO, search engines (with tokenizer, indexer, stemmer, stopwords, > substrings, fuzzy, binary converter...) offer a good choice in terms of > features when it comes to full text search. It seems that Mnogosearch > (http://www.mnogosearch.org) has included sqlite as their default db for > their search engine software. So this might be a good companion to sqlite. > > The best solution would be IMO to have all this optionally integrated into > the database engine. That's what Oracle does with Context and last time I > have used it, it was working very well (it was with Oracle 9i). But that > might also make the engine too heavy. > > Mysql offers fulltext but it is only available AFAIK on the MyISAM table > format. So you can't have foreign key constraints (available on InnoDB > format) and full text at the same time. > > Bertrand Mansion > Mamasam > >
Yes you're right, all full text engines have a ranking mechanism. Some also have term weighting which is very useful especially if it is dynamic. Interestingly, when I first looked at Context some years ago, it was apparent that the full text search functions are not actually part of the core database engine. The Context portion is actually a layer that surrounds the Oracle kernel and uses standard SQL services to achieve the full text capability i.e. for every column that is free text searchable, a surrogate table is created that contains the terms, positions etc of the terms from that column. The Context layer then runs standard SQL queries on this table to affect full text search. I'm only aware of one single true, full text RDBMS and that's BASIS from Open Text. This is the approach I've taken with SQLite and it works pretty well. Going down this road means that it's quite straightforward to combine full text criteria with standard SQL criteria in the same search. I was developing my FT engine to run from a CD so I wasn't interested in keeping my inverted index up to date after deletions/updates to the main table. When you add this in too, the heap of code neccersary to implement this within SQLite gets even longer and more complicated. Steve --------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
