MySQL FULLTEXT search not returning any results

full-text-searchMySQL

I'm wondering why my fulltext search isn't returning any results. To me this seems pretty straightforward, but alas it's not working. I'm far from a DBA, but looking at the EXPLAINed query, I see key_len is zero, which seems odd.

I'm running MySQL 5.5.41

CREATE TABLE

CREATE TABLE `searchable_content` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `page_id` int(11) DEFAULT NULL,
  `menu_title` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `title` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `content` text COLLATE utf8_unicode_ci,
  PRIMARY KEY (`id`),
  FULLTEXT KEY `menu_title` (`menu_title`),
  FULLTEXT KEY `title` (`title`),
  FULLTEXT KEY `content` (`content`)
) ENGINE=MyISAM AUTO_INCREMENT=9 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

Table content

+----+---------+------------+-------+------------------------------------------------+
| id | page_id | menu_title | title | content                                        |
+----+---------+------------+-------+------------------------------------------------+
|  8 |      42 | Apps       | Apps  | <p>blah blah blah blah blabbity blah blah</p>  |
+----+---------+------------+-------+------------------------------------------------+

Query

SELECT 
    page_id, 
    MATCH(`content`) AGAINST ('blabbity') AS 'relevance' 
FROM 
    `searchable_content` 
WHERE 
    MATCH(`content`) AGAINST ('blabbity');

That query EXPLAINed

+----+-------------+--------------------+----------+---------------+---------+---------+------+------+-------------+
| id | select_type | table              | type     | possible_keys | key     | key_len | ref  | rows | Extra       |
+----+-------------+--------------------+----------+---------------+---------+---------+------+------+-------------+
|  1 | SIMPLE      | searchable_content | fulltext | content       | content | 0       |      |    1 | Using where |
+----+-------------+--------------------+----------+---------------+---------+---------+------+------+-------------+

Best Answer

First, ignore the key_len of explain, that value only has a meaning in the context of a BTREE index, so it will always be 0 for a FULLTEXT scan, regardless of the actual size of the index/column.

Second, by default, the FULLTEXT index is used in "natural language mode", which, as it is documented, will ignore results that happen in 50% or more of the rows ("words that are present in 50% or more of the rows are considered common and do not match."). As you only have 1 row, you will always get 0 results. Note that if you add 2 more non-matching records, you will get the desired results:

mysql> insert into searchable_content () VALUES ();
Query OK, 1 row affected (0.00 sec)

mysql> insert into searchable_content () VALUES ();
Query OK, 1 row affected (0.00 sec)

mysql> SELECT
       page_id,
       MATCH(`content`) AGAINST ('blabbity') AS 'relevance'
       FROM
       `searchable_content`  WHERE 
       MATCH(`content`) AGAINST ('blabbity'); 
+---------+--------------------+
| page_id | relevance          |
+---------+--------------------+
|      42 | 0.3573872447013855 |
+---------+--------------------+
1 row in set (0.00 sec)

If you may want to get all results, regardless of the percentage of matches, I recommend you using the boolean mode:

mysql> SELECT
       page_id,
       MATCH(`content`) AGAINST ('+blabbity' IN BOOLEAN MODE) AS 'relevance'
       FROM
       `searchable_content`
       WHERE MATCH(`content`) AGAINST ('+blabbity' IN BOOLEAN MODE);
+---------+-----------+
| page_id | relevance |
+---------+-----------+
|      42 |         1 |
+---------+-----------+
1 row in set (0.00 sec)

Although you lose the ranking. Be careful, as you will never match words shorter than ft_min_word_len (innodb_ft_min_token_size for InnoDB). Changing this variable may require recreate the index.

Also be aware that you could use a single index to search on the three columns at the same time.

Last but not least, fulltext search is available for InnoDB starting with MySQL 5.6, and both 5.6 and 5.7 bring some interesting improvements in terms of performance. If you need a more flexible, feature-complete or performant search solution, you have to go to an external tool like Sphinx or Lucene.

Related Question