MATCH...AGAINST will match some words but not others...

juzzbott · #1 (**permalink**) 05-05-11, 01:27

Hey everyone,

I am having a little trouble with full text search within MySQL, whereby some words will return results, however others will not.

Lets say I have the following table:

Code:

Row    | Title                        | Content
---------------------------------------------------------------------------------
1        | Help topic 1              | Blah blah blahdity blah
---------------------------------------------------------------------------------
2        | Another help topic      | Some other data here

My query essentially looks like this:

SELECT * FROM `table_name` WHERE (MATCH (Title) AGAINST ('search_text*' IN BOOLEAN MODE))

The issue that I am having is that if enter 'topic' as my "search_text", both rows are returned, which is to be expected. If I use 'another' then an empty result is returned.

I don't understand why this is, as it meets the min query length, and stop words should be ignored as I am using the IN BOOLEAN MODE...

Could anyone shed any light as to why this is??

Cheers

r937 · #2 (**permalink**) 05-05-11, 02:16

perhaps not enough rows in the table -- check the manual, it explains this issue

juzzbott · #3 (**permalink**) 05-05-11, 04:06

Quote:

Originally Posted by r937

perhaps not enough rows in the table -- check the manual, it explains this issue

Hi r937,

After some Googling, I could not find anything in the manual that mentions a minimum amount of rows for the query to return results. Could you please give me a point in the right direction?

This seems peculiar behavior as a query should return any rows that match the criteria, regardless of how many rows actually exist within the table...

Cheers

r937 · #4 (**permalink**) 05-05-11, 05:57

Quote:

Originally Posted by juzzbott

Could you please give me a point in the right direction?

pointing ---> http://dev.mysql.com/doc/refman/5.0/...-language.html

Quote:

Originally Posted by da manual

For very small tables, word distribution does not adequately reflect their semantic value, and this model may sometimes produce bizarre results.

If you create a table and insert only one or two rows of text into it, every word in the text occurs in at least 50% of the rows. As a result, no search returns any results. Be sure to insert at least three rows, and preferably many more.

juzzbott · #5 (**permalink**) 05-05-11, 10:25

Quote:

Originally Posted by r937

pointing ---> MySQL :: MySQL 5.0 Reference Manual :: 11.9.1 Natural Language Full-Text Searches

Hi,

Thanks for that information, I think the reason that I missed that information is because that relates to natural language queries. The last sentence of that information is: Users who need to bypass the 50% limitation can use the boolean search mode. As I am using boolean search mode, this behavior seems incorrect.

Either way, I have now added some additional rows, with different data in the columns, and the result is still the same. Some words will return results, others (that are 4 or more characters in length) don't...

Thanks

r937 · #6 (**permalink**) 05-05-11, 13:28

Quote:

Originally Posted by juzzbott

Thanks

you're welcome

Internet Services

Web Development

Digital Marketing

Consumer Tech