Page 1 of 2

Fulltext search problem

Posted: Tue Jan 30, 2007 9:51 am
by user___
Hi guys,
I have a problem with my fulltext search query. It is executed on a MyISAM table and the syntax is OK. There is enough data in the table so this is not the problem. I have put a fulltext index on the column I use for searching.

This is my query:

Code: Select all

SELECT id FROM news WHERE MATCH (title) AGAINST ('Prime'  WITH QUERY EXPANSION)
Any suggestions?

Posted: Tue Jan 30, 2007 10:24 am
by pickle
...and the problem is?

Reply

Posted: Tue Jan 30, 2007 11:05 am
by user___
No matches although there is a title which should match.

Posted: Tue Jan 30, 2007 12:02 pm
by pickle
I've never used the 'WITH QUERY EXPANSION' part. Take that out & see what happens.

Reply

Posted: Tue Jan 30, 2007 12:05 pm
by user___
No matches.

Posted: Tue Jan 30, 2007 12:09 pm
by pickle
How many rows are there in your table? If there's only a few, could you post them here? Also, could you post the results of this query:

Code: Select all

SHOW CREATE TABLE SYNTAX news;

Reply

Posted: Tue Jan 30, 2007 12:33 pm
by user___

Code: Select all

"Table","Create Table"
"news","CREATE TABLE `news` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `title` varchar(39) NOT NULL,
  `user_id` int(10) unsigned NOT NULL,
  PRIMARY KEY  (`id`),
  FULLTEXT KEY `title` (`title`)
) ENGINE=MyISAM AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC"

Posted: Tue Jan 30, 2007 12:45 pm
by Luke
If more than 50% of the rows match, it returns nothing... could that be it?

Posted: Tue Jan 30, 2007 12:48 pm
by pickle
Hmm, I'm drawing a bit of a blank. I recreated your table & threw some dummy data in it. I got the same results. However, when I ran this query:

Code: Select all

SELECT 
  id,
  match(title) 
AGAINST ('Prime' IN BOOLEAN MODE) 
FROM news
on this table:

Code: Select all

+----+-----------+---------+
| id | title     | user_id |
+----+-----------+---------+
|  1 | Prime     |       0 |
|  2 | Prime rib |       0 |
|  3 | Steak     |       0 |
+----+-----------+---------+
I got these results:

Code: Select all

+----+------------------------------------------------+
| id | match(title) against ('Prime' IN BOOLEAN MODE) |
+----+------------------------------------------------+
|  1 |                                              1 |
|  2 |                                              1 |
|  3 |                                              0 |
+----+------------------------------------------------+

That leads me to believe that when searching in boolean mode, it does find a match, but not when its not in boolean mode. Boolean mode can do a search on non-FULLTEXT index columns, so maybe there's something funky with your index? I dropped the index & re-created it, but that didn't alter the results at all.

Sorry man - I'm tapped out of ideas.


EDIT: Hmmm, ~Ninja might be on to something. Lemme check...
EDIT 2: Yep, that's it. I changed my DB to:

Code: Select all

+----+-----------+---------+
| id | title     | user_id |
+----+-----------+---------+
|  1 | Prime     |       0 |
|  2 | Prime rib |       0 |
|  3 | Steak     |       0 |
|  4 | cheese    |       0 |
|  5 | toast     |       0 |
|  6 | rice      |       0 |
+----+-----------+---------+
& got these results when running this search:

Code: Select all

mysql> select match(title) against ('Prime') from news;
+--------------------------------+
| match(title) against ('Prime') |
+--------------------------------+
|               0.68526661396027 |
|               0.68526661396027 |
|                              0 |
|                              0 |
|                              0 |
|                              0 |
+--------------------------------+

Posted: Tue Jan 30, 2007 12:57 pm
by Luke
:teach:

Reply

Posted: Tue Jan 30, 2007 1:35 pm
by user___
Thank you guys but my script does not work yet. I did exactly what you had done but there is no result. Any suggestions? I am running MySQL 5.0.27 and I have never faced any errors like this.

Posted: Tue Jan 30, 2007 1:39 pm
by Luke
how many records are in your table? What term(s) are you searching for? Post some example data (records).

Reply

Posted: Tue Jan 30, 2007 1:54 pm
by user___
I put three rows and insert three value(One per row)

id|title|user_id
1|One|1
2|Two|39
3|Three|3

Code: Select all

SELECT * FROM `news_db`.`news` where match(title) against ("One")
Empty set.

Posted: Tue Jan 30, 2007 2:02 pm
by Luke
"One" is a stop-word.
http://dev.mysql.com/doc/refman/5.0/en/ ... words.html

Problem solved again! :D :drunk:

Posted: Tue Jan 30, 2007 2:07 pm
by pickle
The Ninja Space Goat wrote:"One" is a stop-word.
I was just going to mention that Image

Also, ~user___ if you use [ syntax="sql" ][ /syntax ] tags (without the spaces) rather than [ code ][ /code ] tags, your queries will show up highlighted nicely.