MySQL full text search plural/singular form of words

Question

I have a table like this

CREATE TABLE jobs(
    id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
    title VARCHAR(200),
    body TEXT,
    FULLTEXT (title,body)
) ENGINE=MyISAM;

And two records in this table

...
7. 10 Senior PHP Developers (Leaders) 
8. 30 PHP Developers..
...

And two queries:

1. Return 2 records above

   SELECT * FROM jobs WHERE MATCH (title,body) AGAINST ('developers')

2. Return empty set

   SELECT * FROM jobs WHERE MATCH (title,body) AGAINST ('developer')

I thought that MySQL can found these records with 'developer'. But why it didn't work?

Answer 1

You can switch to full text with boolean operators: http://dev.mysql.com/doc/refman/5.5/en/fulltext-boolean.html

And search for:

SELECT * FROM jobs WHERE MATCH (title,body) AGAINST ('developer*' IN BOOLEAN MODE)

You'll get matches for 'developer' first, then 'developers' or any string starting with 'developer'. It's fine for long precise words as 'developer', but searching 'car*' for example could lead to plenty of unexpected results like 'card', 'cardamon', ....

I don't think there is grammatical analysis in MySQL full text search. I was actually researching this issue today and haven't found anything better.

EDIT

This method won't work for words such as 'party' (plural: 'parties'). If your search form is limited to english, i think you can cover most cases with some simple grammatical rules, and for each word, search the word and its plural. And it it's wrong it will probably lead to an invalid word, which should be neutral in your search.