FULLTEXT Not Returning All Entries

my_name

I created a FULLTEXT index on a column, but for some reason, it's not returning all the appropriate rows when queried.

For example, let's say that I performed a search for the word "knife." About thirty rows should be returned (because the word existed in about that many rows), but instead, only about four rows are returned. Admittedly, all these returned rows are correct (because they all did indeed contain the word "knife"), but the query simply didn't return the remaining 25 that it should have.

I thought it might be an issue of the word being part of other words, but it's definitely not.

Any other ideas why this might be happening?

Thanks!

AIS4U

my name:

It is rather difficult to tell without seeing any code.

my_name

I'm not asking for an exact answer. I just want to know some common reasons why this might happen.

php3ch0

Are you searching FULLTEXT using Boolean mode?

my_name

Nope, doing the simplest one.

Roger_Ramjet

CODE! CODE! CODE!

There are no 'common reasons' why FTS fails except the failure of the coder to implement and use it properly.

my_name

mysql_connect(localhost,$dbusername,$dbpassword);
@mysql_select_db($database) or die( "Unable to select database");
$query = "SELECT * FROM items WHERE MATCH(description) AGAINST (\"" . $keywords . "\")"; 
$result = mysql_query($query);
$TotalRows = mysql_numrows($result);
mysql_close();

Roger_Ramjet

The usual reason why people don't get the results they expect is because they have not read the section on full text search restrictions . In particular, they have not taken in the fact that words that appear in 50% or more rows are ignored. Now my guess is that your 'knife' is in most rows and is ignored, and the results you are getting are for matches against other words in your $keywords.

By the way your query construction syntax is all to pot.

 mysql_connect(localhost,$dbusername,$dbpassword);
@mysql_select_db($database) or die( "Unable to select database");
$query = "SELECT * FROM items WHERE MATCH(description) AGAINST ('$keywords')";
$result = mysql_query($query);
$TotalRows = mysql_numrows($result);
mysql_close(); 

// OR

 mysql_connect(localhost,$dbusername,$dbpassword);
@mysql_select_db($database) or die( "Unable to select database");
$query = "SELECT * FROM items WHERE MATCH(description) AGAINST ('" . $keywords . "')";
$result = mysql_query($query);
$TotalRows = mysql_numrows($result);
mysql_close();

Also, you don't need to use mysqp_close(). PHP uses reference-counting and
"it is automatically detected when a resource is no longer referred to (just like Java). When this is the case, all resources that were in use for this resource are made free by the garbage collector. For this reason, it is rarely ever necessary to free the memory manually by using some free_result function." - like mysql_close()

It would help if, when posting this code you had also posted the $keyword string so that we could see what the final wuery string would be.

my_name

Roger, please refer to the first post: the query was "knife." That's it. The query did return back a few results, all of which had the word "knife" in them (just as expected), but the problem is that it's not returning all rows with the word knife in them. The query is even correctly returning results with "KNIFE" (so it's not a case-sensitivity issue) and "knife." (that is, the word "knife" followed by a period). In addition, the table has about 1800 rows, and only about 35 or so contain the word "knife." The FULLTEXT search is only returning about 5 of those rows, though.

I don't see anything wrong with my query construction. I'm not sure what you were getting at there.

I use mysql_close() just to make sure. It's certainly not hurting anything.