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:
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
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
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
Also, ~user___ if you use [ syntax="sql" ][ /syntax ] tags (without the spaces) rather than [ code ][ /code ] tags, your queries will show up highlighted nicely.