02-18-2009, 11:40 AM
I'm new to FULL-TEXT searching, although I've been programing using MySQL & PHP for a number of years.
I'm trying to do a search on a database, and doing a basic search returns a number of results:
"SELECT * FROM table1 WHERE data like 'test'"
this returns about 20 results, which includes records with both 'Test' & 'test'. However, doing a full-text search returns an empty set:
"SELECT *, MATCH(data) AGAINST ('test') AS score FROM table1 WHERE MATCH(data) AGANIST ('test')"
I have a similar query working that does return results on another table using:
"SELECT ticket_id, MATCH(keywords,details) AGAINST ('test') AS score FROM table2 WHERE MATCH(keywords,details) AGANIST ('test')".
I don't understand how the simple search is returning results, when the full-text doesn't. Am I formatting the query incorrectly?
02-18-2009, 12:05 PM
The fulltext search ignores the results, if the number of records matched is more than 50% of the total number of records. Have a try by adding more records(with different value) in to your table.
Every correct word in the collection and in the query is weighted according to its significance in the collection or query. Consequently, a word that is present in many documents has a lower weight (and may even have a zero weight), because it has lower semantic value in this particular collection. Conversely, if the word is rare, it receives a higher weight. The weights of the words are combined to compute the relevance of the row.
02-18-2009, 01:02 PM
Thanks, that explains why it wasn't working. I've chaned the search to "IN BOOLEAN MODE" to ignore the threshold, which has improved the search, however, I notice on one of my tables one search returns lots of results, and another returns none. My records are:
|010|this is another test|
If I search for 'test' it returns 10 results, if I search for another it returns zero results, even though test is 100% matched and 'another' only 20%.
I have a production table (not a test), that contains support solution data such as 'applied hotfix 1', 'install SP2' etc. If I perform a search on this table, which currently has 30 records, of which only 2 are 'applied SP2' and I search for 'SP2', I get another empty set!
Am I missing something?
02-18-2009, 01:19 PM
Your server's ft_min_word_len might be 4(which is the mysql default)
See http://dev.mysql.com/doc/refman/5.1/en/fulltext-fine-tuning.html (If it's a shared server the possibility to alter those settings is very less)
02-18-2009, 03:14 PM
MySQL is running on our own web server, so it will be possible to change, I just need to find out how to do that.
From looking at http://dev.mysql.com/doc/refman/5.1/en/fulltext-fine-tuning.html am I right in thinking that I have to change the value for ft_min_word_len in the source, then recompile? Is there no way of editing an ini file, or setting it through the command shell?
EDIT: OK, not to worry, managed to google a post and found you can edit my.ini adding 'ft_min_word_len=3' under: [mysqld] - *** MyISAM Specific options, then restarted the MySQL service & then running REPAIR TABLE tbl_name QUICK to re-index.
02-18-2009, 03:22 PM
Find out your my.cnf (mysql configuration) file and add
ft_min_word_len = 3 under [mysqld] . Restart your server and re-index your table.