I have a full text index on a table
articles for the columns
When doing the search on each of the columns, like so
select count(1) from articles where match(content,title,keywords) against ('cats' in boolean mode), the results take between 12 and 15 seconds.
But doing the columns individually (
select count(1) from articles where match(content) against ('cats' in boolean mode)) typically takes less than 50ms.
Why does searching the 3 columns take more than 100 times longer than all of them separately?
This isn't a question of how to make it faster, but instead is more asking "why is it so slow?"
id int(30) PK auto_increment url varchar(1024) title varchar(255) FULLTEXT content text FULLTEXT keywords varchar(1024) FULLTEXT comments text created_date int(11) posted_date int(11)
This first one is the multi-column query:
This second is the new much faster query that runs the 3 columns separately then unions them (query cache was cleared).
Full Text Column Order
Use/Force Index with Explain