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:
Code:
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:
Code:
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?