Postgresql – Search results mismatch after postgresql update

This is not really a use case for full text search because full text relies on stemming the text and parsing the chunks into tokens. As you can see from keywords, '580h' is parsed as its own word because there's no language in which '580' is a "stem" of '580h'. You'd probably be better off with regular expression matching.

Here's a query that I worked up for you:

SELECT id, title 
  FROM stickers WHERE
    (title ~* '580')
      AND
    (title ~* 'case')
ORDER BY id

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.