Full text search not working

[gijew]

I was writing a small search by "this" field function for a small app we have.

 

I simplified it to the point where it should return back SOMETHING yet I'm getting 0 results even after I hard-coded the search criteria. It works just fine on the other table I tried it on so I'm pretty stuck on the idea that I'm missing something in the table setup.

 

Table: MyISAM

Field: filename

Field type: varchar

Max length (255)

Index field: filename (fulltext)

Cardinality: 1

Collation: latin1_swedish_ci

Null: No

Default: ''

 

I've done a `check table`, `analyze table`, and `repair table` all to no avail (based off of things I read about this issue).

 

Like I said, I took all of the fun stuff out of the query to make sure that the query would even run right. I'm getting NO mysql errors either.

 

$SQL = $db->GetAll("SELECT filename FROM prog_wsa_doc_storage WHERE MATCH(filename) AGAINST('sf.csv')");

[gijew]

Hmm, this is a case of getting frustrated before doing better research. Blah.

 

The min fulltext search length is 4 and the string length i'm trying to search for is 5 characters and it's not using any of the default mysql stop words either. I don't know.

 

I suppose I'll just write a LIKE='%string%' query if the fulltext query fails for things like this. Does anyone know of a better way to do this? I don't really want to write 2 queries to do similiar things.

[fenway]

Well, you can recompile the server to change the minimum length....

[gijew]

I thought about changing the server defaults but I'm searching for words greater than 4 characters that aren't on the stopword list so there has to be something else.

 

A few things as far as test cases go are if I search for a name like `watkins` the query returns 0 and the name is in there 1 time. I search for something like `iStock001234.jpg` I get all results; scored. Also, if I search for `sf.csv` I get 0 results so I'm not finding any patterns here. Out of about 500 test records `watkins` is in there once, maybe twice, `sf.scv` is in there about 2 dozen times and `istock(x)` is in there about 50+.

 

I did write a second query based off of using LIKE = 'whatever' if the MATCH fails but both are returning 0

 

I think what I need to do is duplicate this on another server to see if I'm getting the same issue and come back.

[fenway]

LIKE is instead of =

 

Only other robust option is Lucene.

[gijew]

I was working on implementing Lucene via ZF for site search but put it on the back burner due to time constraints.

 

I didn't read much on having it do database lookups. I was under the impression it was better suited for more of a general / flat-file search (albeit more accurate) in comparison to looking for a dozen records inside of a table.