Handling plural keywords using FULLTEXT
A client has complained to me that her search doesn't handle plural and singular keywords interchangeably. I have a single MySQL FULLTEXT column on her products table that contains all searchable content.
There is a chair product on there and if you search chairs, nothing comes up, you have to search just chair. (Note the database is small with only about 300 products). What I have done, which seems to work, is when a search term is entered I create a plural and singular of it and search for them both.
'chair' -> 'chair chairs'
'chairs' -> 'chairs chair'
'sony playstation' -> 'sony playstation sony playstations'
The bits in single quotes on the right would appear in the MATCH part of the query.
It seems to work although I accept that it isn't always grammatically correct. E.g.
'coat for children' -> 'coat for childrens'
Are there likely to be any issues in doing this? I looked into using the BOOLEAN wildcard on the right 'chair*' but that's too inaccurate.
As an aside, on a slightly larger database I have a similar column with FULLTEXT. Not all the records are searchable though. The ones that aren't have a bool column `issearchable` and the FULLTEXT column is NULL. What do you think is more scalable? Using WHERE issearchable = '1' or just deleting the issearchable column and letting MySQL skip over the null FULLTEXT coulmn. Right now, the table isn't big enough to make a noticeable difference when benchmarking. I'm thinking seeing as it's indexed it will be faster for MySQL to skip over null columns rather than first work out which ones are issearchable. Since it's only got a cardinality of 2 I can't index it.