Hmm, I'm drawing a bit of a blank. I recreated your table & threw some dummy data in it. I got the same results. However, when I ran this query:
Code: Select all
SELECT
id,
match(title)
AGAINST ('Prime' IN BOOLEAN MODE)
FROM news
on this table:
Code: Select all
+----+-----------+---------+
| id | title | user_id |
+----+-----------+---------+
| 1 | Prime | 0 |
| 2 | Prime rib | 0 |
| 3 | Steak | 0 |
+----+-----------+---------+
I got these results:
Code: Select all
+----+------------------------------------------------+
| id | match(title) against ('Prime' IN BOOLEAN MODE) |
+----+------------------------------------------------+
| 1 | 1 |
| 2 | 1 |
| 3 | 0 |
+----+------------------------------------------------+
That leads me to believe that when searching in boolean mode, it does find a match, but not when its not in boolean mode. Boolean mode can do a search on non-FULLTEXT index columns, so maybe there's something funky with your index? I dropped the index & re-created it, but that didn't alter the results at all.
Sorry man - I'm tapped out of ideas.
EDIT: Hmmm, ~Ninja might be on to something. Lemme check...
EDIT 2: Yep, that's it. I changed my DB to:
Code: Select all
+----+-----------+---------+
| id | title | user_id |
+----+-----------+---------+
| 1 | Prime | 0 |
| 2 | Prime rib | 0 |
| 3 | Steak | 0 |
| 4 | cheese | 0 |
| 5 | toast | 0 |
| 6 | rice | 0 |
+----+-----------+---------+
& got these results when running this search:
Code: Select all
mysql> select match(title) against ('Prime') from news;
+--------------------------------+
| match(title) against ('Prime') |
+--------------------------------+
| 0.68526661396027 |
| 0.68526661396027 |
| 0 |
| 0 |
| 0 |
| 0 |
+--------------------------------+
Real programmers don't comment their code. If it was hard to write, it should be hard to understand.