Fulltext search problem

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

user___
Forum Contributor
Posts: 297
Joined: Tue Dec 05, 2006 3:05 pm

Fulltext search problem

Post 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?
User avatar
pickle
Briney Mod
Posts: 6445
Joined: Mon Jan 19, 2004 6:11 pm
Location: 53.01N x 112.48W
Contact:

Post by pickle »

...and the problem is?
Real programmers don't comment their code. If it was hard to write, it should be hard to understand.
user___
Forum Contributor
Posts: 297
Joined: Tue Dec 05, 2006 3:05 pm

Reply

Post by user___ »

No matches although there is a title which should match.
User avatar
pickle
Briney Mod
Posts: 6445
Joined: Mon Jan 19, 2004 6:11 pm
Location: 53.01N x 112.48W
Contact:

Post by pickle »

I've never used the 'WITH QUERY EXPANSION' part. Take that out & see what happens.
Real programmers don't comment their code. If it was hard to write, it should be hard to understand.
user___
Forum Contributor
Posts: 297
Joined: Tue Dec 05, 2006 3:05 pm

Reply

Post by user___ »

No matches.
User avatar
pickle
Briney Mod
Posts: 6445
Joined: Mon Jan 19, 2004 6:11 pm
Location: 53.01N x 112.48W
Contact:

Post 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;
Real programmers don't comment their code. If it was hard to write, it should be hard to understand.
user___
Forum Contributor
Posts: 297
Joined: Tue Dec 05, 2006 3:05 pm

Reply

Post 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"
User avatar
Luke
The Ninja Space Mod
Posts: 6424
Joined: Fri Aug 05, 2005 1:53 pm
Location: Paradise, CA

Post by Luke »

If more than 50% of the rows match, it returns nothing... could that be it?
User avatar
pickle
Briney Mod
Posts: 6445
Joined: Mon Jan 19, 2004 6:11 pm
Location: 53.01N x 112.48W
Contact:

Post 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 |
+--------------------------------+
Real programmers don't comment their code. If it was hard to write, it should be hard to understand.
User avatar
Luke
The Ninja Space Mod
Posts: 6424
Joined: Fri Aug 05, 2005 1:53 pm
Location: Paradise, CA

Post by Luke »

:teach:
user___
Forum Contributor
Posts: 297
Joined: Tue Dec 05, 2006 3:05 pm

Reply

Post 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.
User avatar
Luke
The Ninja Space Mod
Posts: 6424
Joined: Fri Aug 05, 2005 1:53 pm
Location: Paradise, CA

Post by Luke »

how many records are in your table? What term(s) are you searching for? Post some example data (records).
user___
Forum Contributor
Posts: 297
Joined: Tue Dec 05, 2006 3:05 pm

Reply

Post 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.
User avatar
Luke
The Ninja Space Mod
Posts: 6424
Joined: Fri Aug 05, 2005 1:53 pm
Location: Paradise, CA

Post by Luke »

"One" is a stop-word.
http://dev.mysql.com/doc/refman/5.0/en/ ... words.html

Problem solved again! :D :drunk:
User avatar
pickle
Briney Mod
Posts: 6445
Joined: Mon Jan 19, 2004 6:11 pm
Location: 53.01N x 112.48W
Contact:

Post 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.
Real programmers don't comment their code. If it was hard to write, it should be hard to understand.
Post Reply