MySQL Forums
Forum List  »  Full-Text Search

can't get fulltext to work
Posted by: Matthew Boehm
Date: July 01, 2005 08:34AM

Ok. I tried the sample table/data that is on the FULLTEXT info page and that works. But using my own data doesn't seem to work:

mysql> show create table customer_notes\G
*************************** 1. row ***************************
Table: customer_notes
Create Table: CREATE TABLE `customer_notes` (
`id` int(11) NOT NULL auto_increment,
`customer_id` int(11) NOT NULL default '0',
`datestamp` int(11) NOT NULL default '0',
`agent_id` int(11) NOT NULL default '0',
`thecomments` text NOT NULL,
UNIQUE KEY `id` (`id`),
FULLTEXT KEY `thecomments` (`thecomments`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql> SELECT id, thecomments FROM customer_notes;
+----+-----------------------------------------------------------------------------------+
| id | thecomments |
+----+-----------------------------------------------------------------------------------+
| 9 | seems like the fax machine at their place works. |
| 8 | harry potter is my favorite book series |
| 7 | hello this is a test sentence to test the search abilities of the search program. |
+----+-----------------------------------------------------------------------------------+
3 rows in set (0.00 sec)

mysql> SELECT id, thecomments FROM customer_notes WHERE MATCH (thecomments) AGAINST ('the');
Empty set (0.00 sec)

That should return 2 rows. But it doesn't.

More examples:

mysql> SELECT id, thecomments FROM customer_notes WHERE MATCH (thecomments) AGAINST ('harry');
+----+-----------------------------------------+
| id | thecomments |
+----+-----------------------------------------+
| 8 | harry potter is my favorite book series |
+----+-----------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT id, thecomments FROM customer_notes WHERE MATCH (thecomments) AGAINST ('the');
Empty set (0.00 sec)

mysql> SELECT id, thecomments FROM customer_notes WHERE MATCH (thecomments) AGAINST ('my');
Empty set (0.00 sec)

mysql> SELECT id, thecomments FROM customer_notes WHERE MATCH (thecomments) AGAINST ('book');
+----+-----------------------------------------+
| id | thecomments |
+----+-----------------------------------------+
| 8 | harry potter is my favorite book series |
+----+-----------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT id, thecomments FROM customer_notes WHERE MATCH (thecomments) AGAINST ('is');
Empty set (0.00 sec)


Seems almost random. What gives?

Options: ReplyQuote


Subject
Views
Written By
Posted
can't get fulltext to work
3435
July 01, 2005 08:34AM
2426
July 01, 2005 09:49AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.