List:General Discussion« Previous MessageNext Message »
From:Don Dikunetsis Date:February 26 2004 6:30pm
Subject:RE: fulltext search always returns no results
View as plain text  
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/

Thread
fulltext search always returns no resultsDon Dikunetsis26 Feb
  • Re: fulltext search always returns no resultsdaniel26 Feb
Re: fulltext search always returns no resultsDon Dikunetsis26 Feb
  • Re: fulltext search always returns no resultsdaniel26 Feb
  • Re: fulltext search always returns no resultsMatt W26 Feb
    • Re: fulltext search always returns no resultsdaniel26 Feb
    • My backup scripts and how reliable are they?Scott Haneda26 Feb
      • Re: My backup scripts and how reliable are they?Adam Hardy27 Feb
        • Re: My backup scripts and how reliable are they?Scott Haneda27 Feb
Re: fulltext search always returns no resultsDon Dikunetsis26 Feb
RE: fulltext search always returns no resultsDon Dikunetsis26 Feb
  • Re: fulltext search always returns no resultsVictoria Reznichenko27 Feb