Why is this MySQL FULLTEXT query returning 0 rows when matching rows are present?
- by Don MacAskill
I have a MySQL table with 200M rows which has a FULLTEXT index on two columns (Title,Body).
When I do a simple FULLTEXT query in the default NATURAL LANGUAGE mode for some popular results (they'd return 2M+ rows), I'm getting zero rows back:
SELECT COUNT(*) FROM itemsearch WHERE MATCH (Title, Body) AGAINST ('fubar');
But when I do a FULLTEXT query in BOOLEAN mode, I can see the rows in question do exist (I get 2M+ back, depending):
SELECT COUNT(*) FROM itemsearch WHERE MATCH (Title, Body) AGAINST ('+fubar' IN BOOLEAN MODE);
I have some queries which return ~500K rows which are working fine in either mode, so if it's result size related, it seems to crop up somewhere between 500K and a little north of 2M.
I've tried playing with the various buffer size variables, to no avail. It's clearly not the 50% threshold, since we're not getting 100M rows back for any result.
Any ideas?
