Unanswered: Full text search does not return expected result
I have a full text search on a table and a FULLTEX index called searchIndex on 3 colums, prod_name, prod_shortdescription, prod_description
When searching the db if the keyword is found in the first field prod_name, the record or records are returned fine, however if the keyword is found in any other fields an empty string is returned, not even the records previously returned are found.
here is the SQL syntax I am using:
SELECT tblproducts.prodId, tblproducts.prod_name, tblproducts.prod_shortdesc, tblproducts.prod_price, tblproducts.taxId,tblproducts.prod_image, tblproducts.prod_status, tbltax.taxRate, tblproducts.prod_discslogan FROM tblproducts, tbltax WHERE MATCH (prod_name, prod_shortdesc, prod_desc) AGAINST ('%keyword%')
Would any one know why this is happening.
Also, any keyword entered must be at least 5 character in length to be considered by default, is it possible to change this, I am working on a shop selling fishing gear meaning than someone searching for rod will see no return whereas there will be 50 rods in the catalogue .
Could you post the details of the index? I assume you are using MyISAM tables?? The 5 character workaroudn is somewhere in the configuration file I believe...it usually defaults to 3 characters if I"m not mistaken, but I need to get it down to 2 characters myself.
Not to nit-pick, but are 'prod_shortdescription' and 'prod_description' the same as 'prod_shortdesc' and 'prod_desc'. I ask because I make typos all the time and I think it would explain the behavior (or not...I think it would produce an error).
good luck...i'll try to post later about the character limitation.
First of all, looking at your query, I think you miss a join between your two tables tblproducts and tbltax, don't you ?
Now, why do you put '%' before and after keyword ? AFAIK, it will search for all words containing your keyword (at the beginning, in the middle or at the end), which should be much slower than searching the entire word directly.
Furthermore, I think your problem comes from here :
mysql> SELECT * FROM articles
-> WHERE MATCH (title,body) AGAINST ('MySQL');
Empty set (0.00 sec)
The search result is empty because the word ``MySQL'' is present in at least 50% of the rows. As such, it is effectively treated as a stopword.
The word you're looking for must be present in too many rows. Maybe the '%' you added make this even worse. If you want to know more, see the MySQL Manual.
You will also find more about the minimum length for a word to be looked for :
Any word that is too short is ignored. The default minimum length of words that will be found by full-text searches is four characters.