Keyword Search Issue in MySQL

[wige]

I am working on creating a search feature for my web site that will allow customers to search for a product based on the name of the item. The current query is basically:

SELECT * FROM products WHERE MATCH(product_name) AGAINST ('keyword');

This method allows the results to be sorted by relevance, and is faster than using LIKE or other types of operators. However, this method does not allow stemming. For example, we have a product line related to a sports team called the IronPigs. Users tend to add a space, searching for "Iron Pigs", causing items containing "IronPigs" not to come up in the results.

The only solution I have been able to find to this is using LIKE:

SELECT * FROM products WHERE product_name LIKE '%keyword%';

This causes the user to get the correct result, but it puts more load on the server, and removes my ability to sort by relevance. For example, we have hundreds of items related to the SuperBowl in the database, but "SuperBowl" itself gets lost in a search, buried under the other, less relevant results.

Is there a way to emulate or enable stemming in MySQL keyword searches, so that the database will look for the keyword within words in the specified field?

[speed]

Stemming does not help with the selection of "Iron Pigs" or "IronPigs" as that is 2 words or 1 word respectively.

Stemming would convert the words directory and directories to directori (may not be the correct stem but illustrates the point).

One option you have is to build a list of common miss spellings then when you get zero results compare the query term against the list and put up a message did you mean ....

Another option is to run the like query if the full text match fails, but then you still have no rank.