yttonk yttonk - 6 months ago 9
SQL Question

MATCH AGAINST on FULLTEXT index returning no rows

I have an online diary, with the two main MyISAM tables being these:

**tbl_log_days**

Name Type
ID int(11)
post_date date
post_content longtext
post_entry_date datetime
post_cat_id int(11)
post_update_date datetime

**tbl_log_cats**

Name Type
fld_id int(11)
fld_cat varchar(255)


The first stores day details, the 2nd stores categories.

I have been experimenting with MATCH AGAINST full text searching following advice from this post:

Advanced text searching using full-text indexes
http://www.hackingwithphp.com/9/3/18/advanced-text-searching-using-full-text-indexes

There is an index on the "tbl_log_days" table:

Keyname: post_content
Type: FULLTEXT
Unique: No
Packed: No
Column: post_content
Cardinality: 2


When I run this search:

SELECT d.ID
, d.post_date
, d.post_content
, d.post_cat_id
, d.post_label
, c.fld_cat
FROM tbl_log_days d
, tbl_log_cats c
WHERE d.post_cat_id = c.fld_id
AND post_content LIKE '%saying%'
AND c.fld_id = 101
AND YEAR(post_date) = 2003
ORDER BY post_date;


It returns 5 rows, and the "post_content" value contains the full word "saying".

When I run this SQL:

SELECT d.ID
, d.post_date
, d.post_content
, d.post_cat_id
, d.post_label
, c.fld_cat
FROM tbl_log_days d
, tbl_log_cats c
WHERE d.post_cat_id = c.fld_id
AND (MATCH(post_content) AGAINST ('saying') AND c.fld_id = 101 AND YEAR(post_date) = 2003)
ORDER BY post_date;


It returns no rows.

This also returns nothing:

SELECT d.ID
, d.post_date
, d.post_content
, d.post_cat_id
, d.post_label
, c.fld_cat
FROM tbl_log_days d
, tbl_log_cats c
WHERE d.post_cat_id = c.fld_id
AND MATCH(post_content) AGAINST ('saying')
AND c.fld_id = 101
AND YEAR(post_date) = 2003
ORDER BY post_date;


And so does this - e.g. removing possible issues with the join to the category table:

SELECT d.ID
, d.post_date
, d.post_content
, d.post_cat_id
, d.post_label
FROM tbl_log_days d
WHERE MATCH(post_content) AGAINST ('saying');


Could there be something wrong with the index - I would have thought if a LIKE '%saying%' returns 5 rows, then a MATCH AGAINST should do as well?

Answer

Let's put something straight:

post_content LIKE '%saying%'

is not the same as

MATCH(post_content) AGAINST ('saying')
  1. The like operator performs simple patter matching. If you have the text 'sayings' in the post content, like will return it. Fulltext search in its default natural language mode will not return this record, since saying is not the same word as sayings.

  2. like operator will return all records matching the pattern, regardless how many records out of the total matches the population. Fulltext search in its default natural language mode will consider any values present in over 50% of the total records as noise and will not return any records matching that criterion only.

  3. There is a minimum word length property applied to the fulltext searches. If your minimum word length is longer than the length of 'saying', then mysql will simply disregard this criterion. like operator does not care about such things.

Mysql has a very detailed documentation on fulltext search, where all the above and a lot more are described.