Where can I find an excellent database search engine script?

Question

I've been looking for PHP, SQL open scripts on retrieving data from DB according to search terms. Now, every script I stumbled upon would not go well at this:

say I'm looking for "barack obama's gay rights policy" ok? but in the DB there's "the obama policy on gays and their rights - an honest article". Albeit this is a pertinent result, a regular search with wildcards wouldn't retrieve anything.

Do you have an idea how to tackle this? I would hate to write one on my own and plus it's an error-prone method. Obviously a ton of websites use this mechanism, I just haven't been lucky at finding anything like that.

Thank you!

Answer 1

You would want to use the databases full text search feature. The implementation should also be able to return the results by how well they match the search terms.
Most databases have this feature, but with MySQL at least, it won't index words less than 4 characters long (by default), which is an issue on shared hosts as they may be unwilling to change it.

If you want higher performance solution or do have a database that doesn't support full text searching, there are dedicated search engines in various languages, such as Lucene

Answer 2

You seem to be looking for a Full-Text search engine of sorts.

DBMS do a great job at storing and retrieving fielded information. As you found out they are more limited when it comes to searching on the basis of keywords, parts of a title etc.

Some DBMSes, offer some built-in or more or less integrated full-text search engine, and this is often a good choice as it allows working with the data (or parts thereof) either with SQL features and predicates, or with FT features.

MySQL does support FullText Search features (thank you Yacoby for the info!!!). See for example the MySQL 5.4 documentation on Full-Text Search functions.
Beware that this feature is said to be slow (see remarks following this answer).

If you want to look outside of MySQL's FT search capabilities, there are many commercial and free, many of them open-source, Full-text engines.

I've worked with and would recommend

• Lucene / Solr
• dtSearch
• MSSearch

but you should look into a more complete list at this Wikipedia article on this topic.

Answer 3

I would recommend Sphinx (works for MySQL, PostreSQL and Firebird)