Hi,
The comments in the fulltext doc page
(http://www.mysql.com/doc/en/Fulltext_Search.html) discuss the issues of
stopwords and over 50% hits, so I did my best to avoid those particular
bombs in my searches.
The "subject" column contains subjects for message posts/entries, and as
such they're strings of around six words, on average. Here's some searches
that returned blank results:
SELECT subject FROM entry WHERE MATCH (subject) AGAINST ('your');
SELECT subject FROM entry WHERE MATCH (subject) AGAINST ('spam');
SELECT subject FROM entry WHERE MATCH (subject) AGAINST ('hatching');
"your" is a possible stopword, but the other two are words that appear just
once in the 16 (now 19) records.
For the list in general, here's some things I tried since my last post:
1. Attempted to verify that the table is MyISAM. CHECK TABLE and ANALYZE
TABLE were processed okay, which _seems_ to indicate that the table is
MyISAM:
CHECK TABLE entry; LIMIT 0, 30
Table Op Msg_type Msg_text
------------------------------------
entry check status OK
ANALYZE TABLE entry; LIMIT 0, 30
Table Op Msg_type Msg_text
----------------------------------------
entry analyze status Table is already up to date
2. Based on a comment in
(http://www.mysql.com/doc/en/Fulltext_Fine-tuning.html), ran:
ALTER TABLE entry TYPE=MyISAM;
Result: command returned without an error; however, searches still come up
blank.
3. Noted in the fulltext restrictions doc
(http://www.mysql.com/doc/en/Fulltext_Restrictions.html) that fulltext
before 4.1.1 doesn't work with Unicode.
To check the current settings, tried running SHOW CHARACTER SET; and SHOW
COLLATION;, but got errors:
MySQL said: You have an error in your SQL syntax near 'CHARACTER SET' at
line 1
MySQL said: You have an error in your SQL syntax near 'COLLATION' at line 1
So I flushed the data from the table, and reloaded with data with a
character encoding explicitly set at iso-8859-1. However, my searches still
return blank results.
I must admit that at this point I'm stumped!
>From: "Peter Lovatt" <pjl@stripped>
>To: "Matt W" <mysql_lists@stripped>, "Don Dikunetsis"
><ddikun722@stripped>, <mysql@stripped>
>Subject: RE: fulltext search always returns no results
>Date: Thu, 26 Feb 2004 07:57:21 -0000
>
>Hi
>
>Full text can have some strange results.
>
>It ignores common words, like the, if, and, etc as they are not relevant.
>
>If you have a word that appears in most records then it will ignore it,
>sometimes returning 0 results even if it is what you were looking for!
>
>The aim of full text is to return the most relevant records. If there are
>no
>records that are more relevant than any others it may return none.
>
>Could this be the cause?
>
>Peter
>
>
>-----------------------------------------------
>Excellence in internet and open source software
>-----------------------------------------------
>Sunmaia
>Birmingham
>UK
>www.sunmaia.net
>tel. 0121-242-1473
>International +44-121-242-1473
>-----------------------------------------------
>
>
>
>
>
>
>
>
>
>
>-----Original Message-----
>From: Matt W [mailto:mysql_lists@stripped]
>Sent: 26 February 2004 07:26
>To: Don Dikunetsis; mysql@stripped
>Subject: Re: fulltext search always returns no results
>
>
>Hi Don,
>
>No, full-text search was added in MySQL 3.23.23, I believe (4.0.1 just
>added boolean searches along with more speed overall). It doesn't need
>to be compiled in or anything, it's there by default. Unless someone
>compiled it and actually *removed* the full-text code or something. :-)
>
>Also, key_len of 0 in EXPLAIN is normal.
>
>It sounded like you are getting some kind of error in your first
>message? If so, what is it? Are you SURE that the EXACT word you're
>searching for is present in the table (for example, with a space, etc.
>on either side of it)?
>
>
>Matt
>
>
>----- Original Message -----
>From: "Don Dikunetsis"
>Sent: Thursday, February 26, 2004 12:21 AM
>Subject: Re: fulltext search always returns no results
>
>
> >
> > Hi, thanks for your reply, but it looks like:
> >
> > > As of Version 3.23.23, MySQL has support for full-text indexing and
> > searching.
> >
> > --according to:
> >
> > http://www.mysql.com/doc/en/Fulltext_Search.html
> >
> > However, I would be unsurprised (though disappointed) to find that the
> > answer is some variant of "this won't work with your
>configuration"--my
> > setup certainly doesn't seem to be responding to the normal setup
> > instructions for fulltext search.
> >
> >
> > >From: <daniel@stripped>
> > >Subject: Re: fulltext search always returns no results
> > >Date: Thu, 26 Feb 2004 10:12:42 +1100 (EST)
> > >
> > >Excuse if i'm not correct but this may be your problemo ?
> > >
> > >MySQL 3.23.55 running on my webhost's Linux box
> > >phpMyAdmin 2.1.0
> > >
> > >
> > >I didnt think fulltext was in 3.23 wasnt this a Mysql 4 feature ??
> > >
> > >
> > > > Summary: When I run a fulltext search, it always returns no
>results. I
> > > > have added a fulltext index to the column being searched. Also, I
>am
> > > > searching for a term that is in the table, but not in more than
>50% of
> > > > the rows.
> > > >
> > > > I notice that when I add EXPLAIN to my search, the key_len of my
> > > > fulltext index is 0. Does that mean my fulltext index is empty,
> > > > explaining why my searches never return any results?
> > > >
> > > > Either way, I'd be grateful for any suggestions on how to get
>fulltext
> > > > search to work!
_________________________________________________________________
Find and compare great deals on Broadband access at the MSN High-Speed
Marketplace. http://click.atdmt.com/AVE/go/onm00200360ave/direct/01/
