[BNM] Suggest in search - way of doing with MySQL?

[Alastair James]

Sorry,

"I.e. it might suggest a sentence
that does not occur in that order. If you are searching in a strict
mode, this suggested query will still produce no results!"

Should read:

"I.e. it might suggest a sentence
with words that do not occur together in the same record. If you are
searching in a strict
mode, this suggested query will still produce no results!"

Al

2008/10/2 Alastair James <al.james at gmail.com>:
> HI...
>
> +1 Not using mysql fulltext. Ever.
>
> If you dont want to use lucene, I would suggest looking at sphinx
> search. http://www.sphinxsearch.com/.
>
> Its a nice little full text engine. Its biggest problem is that it is
> hard to set up to work with live updates (i.e. you need to batch
> update every so often so make sure your code can cope with the
> fulltext index being out of sync without crashing). When you update
> you can get a list of words (and their frequencies) and store in a
> file / table. Then its pretty easy to split the query into words and
> compare each to the words in the index file using the levenshtein
> function.
>
> Note, however, that solutions based on individual words (as opposed to
> n-grams, groups of words) might suggest combinations of words that
> dont occur together in the dataset. I.e. it might suggest a sentence
> that does not occur in that order. If you are searching in a strict
> mode, this suggested query will still produce no results!
>
> I would have a look at the Zend implementation of lucene, I have not
> used it but I hear its good, but not that fast.
>
> Al
>
> 2008/10/2 Mark Ng <mark at markng.me.uk>:
>> Oh, one extra bonus - mySQL full text searching algorithms can
>> sometimes be quite slow (depending on the search).  If you're working
>> on a large (traffic wise) site, externalising the search into Lucene
>> is 1) faster and 2) means you can take the weight of search away from
>> the DB (which is the hardest thing to parallellize.)
>>
>> 2008/10/2 Mark Ng <mark at markng.me.uk>:
>>> First, I'd suggest indexing whatever it is you need to index with some
>>> form of Lucene implementation (Zend has one for PHP).  The mysql full
>>> text indexing is useful in some situations, but it requires myISAM
>>> tables (which aren't always the best move) and Full-Text indexing is a
>>> pain when you have to search across multiple fields and even more so
>>> across multiple tables.  Lucene indexing allows you to prioritise
>>> certain fields (so, for example, you can automatically weight words in
>>> a title field), and does a much better job of relevancy searching.
>>>
>>> You can then use levenshtein (as suggested elsewhere) distances to
>>> suggest other words from the index if you want to, but Lucene has this
>>> fuzzy matching already built in, so if you add a tilde to the search
>>> query, it'll use those techniques to do this automatically for you.
>>>
>>> HTH,
>>>
>>> Mark
>>>
>>> 2008/10/2 Paul Silver <paul at tenpastmidnight.com>:
>>>> Hi,
>>>>
>>>> I'm making a search within a database driven site soon. We'd like to be
>>>> able to suggest corrections or just show results for the suggestions if
>>>> nothing is returned from a straight search.
>>>>
>>>> E.g. visitor searches for "bananas in pijamas"
>>>>
>>>> Results say: "No results for 'bananas in pijamas', would you like to
>>>> search for 'banans in pyjamas'"
>>>>
>>>> Is there an easy way of doing this with PHP 5 & MySQL 5?
>>>>
>>>> Thanks
>>>>
>>>> Paul
>>>> --
>>>>  Paul Silver - tel: 01273 906020 or mobile: 07813 654285
>>>>  Web Development: http://www.paulsilver.co.uk
>>>>  Search Engine Promotion: http://webpositioningcentre.co.uk
>>>>  Check your web pages: http://www.spidertest.com
>>>>
>>>> --
>>>>
>>>> BNM Subscribe/Unsubscribe:
>>>> http://www.brightonnewmedia.org/options/bnmlist
>>>>
>>>> BNM powered by Wessex Networks:
>>>> http://www.wessexnetworks.com
>>>>
>>>
>> --
>>
>> BNM Subscribe/Unsubscribe:
>> http://www.brightonnewmedia.org/options/bnmlist
>>
>> BNM powered by Wessex Networks:
>> http://www.wessexnetworks.com
>>
>
>
>
> --
> Dr Alastair James
> CTO James Publishing Ltd.
>
> www.worldreviewer.com
> Winner Yahoo! Finds of the Year
> WINNER Travolution Awards Best New Online Travel Company 2008
> "In a market increasingly crowded with new content and experience-led
> sites, Worldreviewer stands out a mile. It has used exclusive
> editorial and excellent tools to wonderful effect and ensured its
> commercial partnerships are relevant to the target market."
>
> Blogs: onewheeledbicycle.com, traveltelegraph.com
>
> "Utinam logica falsa tuam philosophiam totam suffodiant!"
>



-- 
Dr Alastair James
CTO James Publishing Ltd.

www.worldreviewer.com
Winner Yahoo! Finds of the Year
WINNER Travolution Awards Best New Online Travel Company 2008
"In a market increasingly crowded with new content and experience-led
sites, Worldreviewer stands out a mile. It has used exclusive
editorial and excellent tools to wonderful effect and ensured its
commercial partnerships are relevant to the target market."

Blogs: onewheeledbicycle.com, traveltelegraph.com

"Utinam logica falsa tuam philosophiam totam suffodiant!"