full text Search not searching

[alohaaaron]

Hi, I'm trying to speed up my query by indexing for full-text and doing a full-text search but when I search by full-text nothing is returned.

SELECT * from my_table WHERE MATCH(column_a) AGAINST ('WILLIAM*'); //nothing is returned

column_a contains WILLIAMABC and is of type varchar (20)

if I run a query:
SELECT * from my_table WHERE column_a LIKE 'WILLIAM%'; it works great.

Do I have the syntax correct?
Thanks!

 

[Kozusnik]

Try +WILLIAM* with a plus sign in the front.

When I do full text searches, the surrounding double quotes means "full phrase", each word individually means "any word", and the + before each word, means it "must exist in the result set".

I'm afraid that if you use WILLIAM*, if will assume that the asterisk is a character.

Mark

 

[alohaaaron]

Thanks but it doesn't seem to work. SQL wants the quotes. If I don't include single or double quotes I get an error.

Error Code : 1064
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ')' at line 1
(0 ms taken)

 

[Kozusnik]

Try this...

SELECT * from my_table WHERE MATCH(column_a) AGAINST ('+WILLIAM*' IN BOOLEAN MODE);

When you add the +, -, *, etc., "IN BOOLEAN MODE" is required.

Mark