I've been trying to find some help on using MySQL's FULLTEXT search. I realise that this has been discussed to death, but I can't quite understand how to get a concise set of results.
I have a MyISAM table of say 500,000 products with a FULLTEXT index setup on the "product_name" table.
A basic query would be:
SELECT * from products MATCH(product_name) AGAINST ("coffee table") AS relevance
WHERE MATCH(product_name) AGAINST ("coffee table").
I got a list of a few hundred products that relate to either coffee or tables. This wasn't specific enough and meant that useful results were cluttered with too many other items.
I altered my query to use MATCH to give a relevance to each result, and then used LIKE to perform the actual query.
SELECT * from products MATCH(product_name) AGAINST ("coffee table") AS relevance
WHERE ((product_name like "%coffee%" AND product_name like "%table%") or product_name like "%coffee table%")
This idea I got from seeing how Wordpress performs a search. This worked well until someone performs a search with more specific keywords. A real-world example was a search for "Nike blazer low premium vintage". In this case, there were no results (whereas the first method using MATCH returned hundreds)
I know I can use IN BOOLEAN MODE, but many users won't know to use the +/- operators to alter their query. I'm yet to work out how I should use the HAVING clause to limit results.
Also, due to this being shared hosting, I am unable to alter the default min word length - which means missing keywords like the colour "red" or the brand-name "GAP" for example.
I have read a little into creating a keyword index table, but have not found suitable references for this.
Can someone please offer a solution where I can use a product search term (as entered by Joe Public) that will give a concise set of results. Thanks
I have done more research and as many people have said, it's not a good solution for "human" like searching - one example is how it handles word plurals (car / cars). I looked at Apache Lucene but it's beyond my ability to setup and configure.
For the moment, the "solution" has been to stick with IN BOOLEAN MODE (as Mathieu also suggested).