Bug #23886 Trouble with full-text search.
Submitted: 2 Nov 2006 13:48 Modified: 3 Nov 2006 19:18
Reporter: massimo cordisco Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server: FULLTEXT search Severity:S1 (Critical)
Version: OS:Windows (windows 2003)
Assigned to: CPU Architecture:Any

[2 Nov 2006 13:48] massimo cordisco
Description:
I've a trouble with full-text search. 
I've created a full-text index composed by four fields : 
TITLE_CONTENT,KEYWORDS_CONTENT,DESCRIPTION_CONTENT,PAGE_CONTENT. 
In the field PAGE_CONTENT is present the word >>manga<<, so, i try to execute a query here reported,but the response is zero rows.... 
How is it possible? 
And this happen not only for this key but for others too. 

this is the query: 

SELECT *, MATCH(TITLE_CONTENT,KEYWORDS_CONTENT,DESCRIPTION_CONTENT,PAGE_CONTENT) AGAINST('manga') AS attinenza FROM tabella WHERE MATCH(TITLE_CONTENT,KEYWORDS_CONTENT,DESCRIPTION_CONTENT,PAGE_CONTENT) AGAINST('manga') 

the connection is throw odbc.

thank very much.

Massimo.

How to repeat:
SELECT *, MATCH(TITLE_CONTENT,KEYWORDS_CONTENT,DESCRIPTION_CONTENT,PAGE_CONTENT) AGAINST('manga') AS attinenza FROM tabella WHERE MATCH(TITLE_CONTENT,KEYWORDS_CONTENT,DESCRIPTION_CONTENT,PAGE_CONTENT) AGAINST('manga')
[2 Nov 2006 19:14] Sveta Smirnova
Thank you for the report.

Please, provide output of SHOW CREATE TABLE tabella and accurate version of MySQL server you use.
[2 Nov 2006 19:29] massimo cordisco
Mysql version : 4.1.12

field      type          
URL_PAGE   varchar(255)
SITE_NUMBER   int(6)
SITE_SECTION   tinyint(4)
SITE_SUBSECTION   tinyint(4)
TITLE_CONTENT   text
KEYWORDS_CONTENT   text
DESCRIPTION_CONTENT   text
PAGE_CONTENT   longtext
VISUALIZATIONS   int(9)

Index
PRIMARY  URL_PAGE  

FULLTEXT INDEX(TITLE_CONTENT,KEYWORDS_CONTENT,DESCRIPTION_CONTENT,PAGE_CONTENT)

thank you

Massimo.
[2 Nov 2006 19:42] Sveta Smirnova
Version 4.1.12 is old. Please try new 4.1.21 version accessible from http://dev.mysql.com/downloads/mysql/4.1.html and if you can repeat the problem provide exact output of SHOW CREATE TABLE tabella statement, not just field names and types.
[2 Nov 2006 19:56] massimo cordisco
Hi Sveta,
the mysql db is on a remote server, so i've to comunicate to refresh the version to the administrator...

CREATE TABLE `blf_section_015` (\n  `URL_PAGE` varchar(255) NOT NULL default '',\n  `SITE_NUMBER` int(6) default NULL,\n  `SITE_SECTION` tinyint(4) default NULL,\n  `SITE_SUBSECTION` tinyint(4) default NULL,\n  `TITLE_CONTENT` text,\n  `KEYWORDS_CONTENT` text,\n  `DESCRIPTION_CONTENT` text,\n  `PAGE_CONTENT` longtext,\n  `VISUALIZATIONS` int(9) default NULL,\n  PRIMARY KEY  (`URL_PAGE`),\n  FULLTEXT KEY `TITLE_CONTENT` (`TITLE_CONTENT`,`KEYWORDS_CONTENT`,`DESCRIPTION_CONTENT`,`PAGE_CONTENT`)\n) ENGINE=MyISAM DEFAULT CHARSET=latin1

Thank you.

Massimo
[3 Nov 2006 8:30] Sergey Vojtovich
Hi!

Words that are present in more than 50% of the rows are considered common and do not match. Make sure that "manga" is not one of these common words.
[3 Nov 2006 11:16] massimo cordisco
Where can i look if the word >manga< is one these common words?

Thanks.

Massimo.
[3 Nov 2006 11:29] Sergey Vojtovich
You can try to get number of rows that contains word "manga" and number of rows in a table:
SELECT COUNT(*) FROM table;
SELECT COUNT(*) FROM table WHERE
MATCH(TITLE_CONTENT,KEYWORDS_CONTENT,DESCRIPTION_CONTENT,PAGE_CONTENT)
AGAINST('+manga' IN BOOLEAN MODE);
[3 Nov 2006 13:31] massimo cordisco
ok
SELECT COUNT(*) FROM table;
result = 30

SELECT COUNT(*) FROM table WHERE
MATCH(TITLE_CONTENT,KEYWORDS_CONTENT,DESCRIPTION_CONTENT,PAGE_CONTENT)
AGAINST('+manga' IN BOOLEAN MODE);
result = 30

so,if i search the word manga in my db, i get no result? what can i do?

another question....

i've these records in the table blf_glb_img
the field is IMG_SRC

http://www.lacucinadimammateresa.it/theme/images/ambiente/200/bar.jpg
http://www.lacucinadimammateresa.it/theme/images/ambiente/200/sala1.jpg
http://www.lacucinadimammateresa.it/theme/images/ambiente/150/sala2.jpg
http://www.lacucinadimammateresa.it/theme/images/ambiente/150/sala3.jpg
http://www.lacucinadimammateresa.it/theme/images/ambiente/150/sala4.jpg
http://www.lacucinadimammateresa.it/theme/images/mappa/mappaSmall.jpg
http://www.lacucinadimammateresa.it/theme/images/Menu/Antipasto150.jpg
http://www.lacucinadimammateresa.it/theme/images/Menu/Primo150.jpg
http://www.lacucinadimammateresa.it/theme/images/Menu/SecondoCarne150.jpg
http://www.lacucinadimammateresa.it/theme/images/Menu/SecondoPesce150.jpg
http://www.lacucinadimammateresa.it/theme/images/Menu/Contorno150.jpg
http://www.lacucinadimammateresa.it/theme/images/Menu/Formaggi150.jpg
http://www.lacucinadimammateresa.it/theme/images/Menu/Dolci150.jpg

i try to extract these records with this query for key : >cucina< , but i get nothing...
i've tried in boolean mode too....

SELECT *,MATCH(IMG_SRC,IMG_ALT) AGAINST('cucina') FROM blf_glb_img WHERE MATCH(IMG_SRC,IMG_ALT) AGAINST('cucina')

thanks.

Massimo.
[3 Nov 2006 19:18] Sveta Smirnova
We're sorry, but the bug system is not the appropriate forum for asking help on using MySQL products. Your problem is not the result of a bug.

Support on using our products is available both free in our forums at http://forums.mysql.com/ and for a reasonable fee direct from our skilled support engineers at http://www.mysql.com/support/

Thank you for your interest in MySQL.