Building search functionality using MySQL

[MitchV]

I'll start by admitting that I have done very little with MySQL. The extent of my experience has been importing/exporting data between MySQL databases and Microsoft's SQL Server.

My mission in coming over here is to ask how MySQL handles searches as in the example here:

http://www.vbulletin.org/forum/search.php?s=79fde36a902dad4b9af6254aea1221ab&

If you search for the word "the" or "like", it's smart enough to exclude the words and doesn't kill the database.

The search is in real-time, so if I enter a message with the word "froghair" and search for it seconds later, it's there.

Does MySQL have any full-text search functionality like Microsoft SQL Server? For example, if I search for the word "goose", does MySQL have anything built in that would make it bring back something with "geese" in it?

I'm trying to build a real-time search for SQL Server that can accomplish this, but I don't think the functionality is built into Microsoft SQL Server. I could build a dynamic query using like '%searchword%' and an exclude list... but that's the best I can come up with.

Is that how the example of the MySQL search I linked to does it? Using an exclude list and a dynamic query? Is there anything else built into MySQL to provide that functionality?

Lastly, is there a good list of English words to exclude from searches?

Thanks!
Mitch

 

[vanekl]

Yes, MySQL has full-text search capabilities.

Yes, MySQL has a stopword list that allows you to filter out
words like 'the.'

Yes, version 4.1.1 has query expansion that should pull up
records with "geese" in them when searching for "goose."

Yes, the full-text search is dynamic.

You may want to take a look at the documentation

http://www.mysql.com/documentation/mysql/bychapter/manual_SQL_Syntax.html#Fulltext_Search