CODEWITHSUNDEEP
mysqlfull-text-indexing
q.Then
q.Then

Reputation: 2751

MySQL Match unexpected behavior

I have a database of a list of all the PHP functions in an MyISAM table whose columns function_name and description are indexed using the FULL TEXT index. I'm getting some.. weird behavior with the MATCH...AGAINST() query, for example:

select * from functions where match(function_name) against('array_search');
select * from functions where function_name = 'array_search'

Returns the expected rows, each query returning one row, but then:

select * from functions where match(function_name) against('each');
select * from functions where function_name = 'each'

The second query only returns one row, while the first query returns nothing at all.

I've also noticed that this tends to happen for "common" words in the description column (which should be irrelevant), for example, the same thing happens when searching for "while" but not for something like "array_pop". However, "count" does return the expected behavior.

Also, the rows it gets unexpected behavior are rows I have newly inserted, for the most part (almost all the newly inserted rows do not work, but I'm not sure if all the rows that do not work are newly inserted)

The only newly inserted row that does work are bizarre function names like:

select * from functions where match(function_name) AGAINST('asdadasdadasdadasdadasdad')

Returns the expected behavior. I have tried executing something like:

ALTER TABLE functions DROP INDEX description;
ALTER TABLE functions DROP INDEX function_name;
ALTER TABLE `functions` ADD FULLTEXT(`function_name`);
ALTER TABLE `functions` ADD FULLTEXT(`description`);
select * from functions where match(function_name) against('each');
select * from functions where function_name = 'each';

Which still returns only one row on the last query and no rows for the MATCH() ...AGAINST query. I'm not sure what the math behind MATCH...AGAINST query is, but does anyone know the reason I might be running into this?

Upvotes: 1

Views: 137

Answers (1)

Mihai
Mihai

Reputation: 26804

You are running into stop words,namely very common words which are ignored.Scroll further down for MyISAM,each is in there.You can edit that list or use an empty file to ignore any stop words.

Upvotes: 1

Related Questions