Multi-Column Full Text Search Going Very Slow

I have a full text index on a table articles for the columns content, title and keywords

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?"

Table/Indexes

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)

Explains

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

PROBLEM

From the posts in your question, I see 3 FULLTEXT indexes. There is one for each column.

Why did the query work at all ? MySQL worked with whatever it had. In your case, it searched by a full table scan. That's what the MySQL Query optimizer decided on.
SOLUTION

What you really need is a single FULLTEXT index with all 3 columns

ALTER TABLE articles ADD FULLTEXT content_title_keywords_ndx (content,title,keywords);

Only then can you say

match(content,title,keywords) against ('cats' in boolean mode)

I have suggested making compound FULLTEXT indexes before

• Mar 16, 2012 : Speed up search across multiple columns

• Oct 13, 2012 : Can underscore be forced as a word splitter without a full-text parser plugin?

• All my posts about FULLTEXT indexing and searching

GIVE IT A TRY !!!

StackExchange Database Administrators Q#74688, answer score: 6