Search code examples
mysqlmatch-against

MySQL table MATCH AGAINST

enter image description here

Hi all,

I have this simple table created, called classics in a DB called, publications on XAMMP. I am trying to do a MATCH AGAINST search for an author name which i thought I understood.

Also, I have made sure the table is FULLTEXT indexed, both author and title columns as required. The table is of the type MyISAM also.

I tried this and it failed.

SELECT author FROM classics WHERE MATCH(author) AGAINST('Charles');

I know Charles must be present in the author column and it is as you an see but i get no rows returned.

Now if I rewerite it to any other author, it works

SELECT author FROM classics WHERE MATCH(author) AGAINST ('jane');

Here is what i get with jane...

enter image description here

I'm not sure but it seemed earlier i had to included both fields I'd indexed in the query, instead of just being able to search author alone. Is this correct and does anyone know why I can't get charles returned?.

Many thanks!.

Solution

  • It's not returning those rows because "charles" appears in 50% of the rows. This is a well-documented restriction of MySQL FULLTEXT search.

    If you want to get around this restriction, you can use BOOLEAN MODE.

    Here's the relevant excerpt from the manual:

    A word that matches half of the rows in a table is less likely to locate relevant documents. In fact, it most likely finds plenty of irrelevant documents. We all know this happens far too often when we are trying to find something on the Internet with a search engine. It is with this reasoning that rows containing the word are assigned a low semantic value for the particular data set in which they occur. A given word may reach the 50% threshold in one data set but not another.

    The 50% threshold has a significant implication when you first try full-text searching to see how it works: 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. Users who need to bypass the 50% limitation can use the boolean search mode; see Section 12.9.2, “Boolean Full-Text Searches”.