MySQL WHERE MATCH problem

[Maziar Aflatoun]

Hi,

I have the following table

CREATE TABLE `fulltext_sampl e` (
`copy` text,
FULLTEXT KEY `copy` (`copy`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

INSERT INTO `fulltext_sampl e` VALUES ('This is a test to see how mysql
works');

Now I do a search for
SELECT * FROM fulltext_sample WHERE MATCH(copy) AGAINST('mysql' );
and it return 0 results. Can someone please help me with this?

Thanks
Maz.

[Mike Willbanks]

Re: MySQL WHERE MATCH problem

Maziar,
[color=blue]
> I have the following table
>
> CREATE TABLE `fulltext_sampl e` (
> `copy` text,
> FULLTEXT KEY `copy` (`copy`)
> ) ENGINE=MyISAM DEFAULT CHARSET=latin1;[/color]

First you should always use an id field now...

If you look at the mysql manual for fulltext
(http://dev.mysql.com/doc/mysql/en/fulltext-search.html) the correct
way to create the table would be:
CREATE TABLE fulltext_sample (
id id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
copy TEXT,
FULLTEXT (copy)
);

What you pretty much did wrong at the top was trying to create a key. I
do not know exactly how mysql parses that but you may want to try over
with an ID column and the column for a copy.

Mike

[Mike Willbanks]

Re: MySQL WHERE MATCH problem

Maziar,
[color=blue]
> What you pretty much did wrong at the top was trying to create a key. I
> do not know exactly how mysql parses that but you may want to try over
> with an ID column and the column for a copy.[/color]

Also one more quick note. From the manual:
" The search result is empty because the word ``MySQL'' is present in at
least 50% of the rows. As such, it is effectively treated as a stopword.
For large datasets, this is the most desirable behavior---a natural
language query should not return every second row from a 1GB table. For
small datasets, it may be less desirable."

Mike