How can I match "laptop" and "laptops" when search for "laptop" is performed using MySQL's match...against full-text search?

Question

I'm trying to build a search feature in to my website. Searching is the primary purpose of the site and I am having a few issues with the MySQL search feature.

I would like to be able to search with a single search string against multiple text fields.

I'm using MySQL's match...against statements to perform a full-text search. I'm running this in Boolean mode.

My SQL looks something like this:

SELECT * FROM ... 
WHERE MATCH(table.field1, table.field2, table.field3) 
                           AGAINST ('laptop' IN BOOLEAN MODE)

Currently this returns results that have the word laptop but not if they have the word laptops.

I need it to return results that contain the word laptop or laptops.

Answer 1

use the wildcard * on the end of your phrase

SELECT * FRO... WHERE MATCH(table.field1, table.field2, table.field3) AGAINST ('laptop*') IN BOOLEAN MODE)

Answer 2

If your requirements are more general, requiring full stemming support e.g. matching "testing" or "tests" against test in the full text index for example then you can use an alternative full text index plugin. A google search pulls up a number of possibilities although a quick glance suggests that most are commercial rather than open source.

Answer 3

If you're using MySQL 5.1, you can install a stemmer plugin.

• http://forge.mysql.com/projects/project.php?id=215
• http://forge.mysql.com/projects/project.php?id=42