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" <[EMAIL PROTECTED]>
To: "Matt W" <[EMAIL PROTECTED]>, "Don Dikunetsis" <[EMAIL PROTECTED]>, <[EMAIL PROTECTED]>
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:[EMAIL PROTECTED] Sent: 26 February 2004 07:26 To: Don Dikunetsis; [EMAIL PROTECTED] 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: <[EMAIL PROTECTED]> > >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/
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
