MySQL FULLTEXT search not returning any results

Question

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 |
+----+-------------+--------------------+----------+---------------+---------+---------+------+------+-------------+

Answer 1

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.

• Michael - sqlbot: I always assumed that the rows and key_len are both set to artificially low values as a naive (but effective) tactic to persuade the optimizer that choosing a plan involving the fulltext index would be a really, really, really good idea. Of course, what other option could there be? None, right? As the source file storage/innobase/handler/ha_innodb.cc points out right before setting the "1" in "rows," /* The whole concept has no validity for FTS indexes. */ The only time I recall seeing fultext not preferred was a unique (btree) index with zero matching rows.
• Quasipickle: Excellent answer - thank you. Of course I missed the ""words that are present in 50% or more of the rows are considered common and do not match."
• King Friday: good show, amazing how the 50% of the rows idea is so esoteric, how in the heck would anyone guess that in local testing? crazy.