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


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:

enter image description here

This second is the new much faster query that runs the 3 columns separately then unions them (query cache was cleared).

enter image description here

Full Text Column Order

enter image description here

Use/Force Index with Explain

enter image description here

  • What's your table and index definitions? You can use EXPLAIN to get details about query plan and execution steps and timings.
    – Stoleg
    Aug 23, 2014 at 0:20
  • Can you also please add EXPLAINs for both queries (ideally 4). How long does it take to search content column?
    – Stoleg
    Aug 26, 2014 at 16:34
  • @Stoleg, I updated the question. Aug 26, 2014 at 16:52
  • The reason is simple - FT index is not used. In first query put columns in the same order as index: title, content, keywords.
    – Stoleg
    Aug 26, 2014 at 18:34
  • Screenshotted "Show Indexes from articles". My query is in the proper order. Aug 26, 2014 at 19:04

1 Answer 1



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.


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


  • That worked like a charm. Can't believe it took 2 days for that ;). Aug 26, 2014 at 21:17
  • I am sorry I took so long to get to it. Aug 26, 2014 at 21:18
  • @RolandoMySQLDBA is the order of columns, in the index, is important at all? I.e. will there be difference between match(content,title,keywords) and match(keywords, content,title) when having the same index content_title_keywords_ndx (content,title,keywords) ? Thanks
    – Andrew
    Aug 27, 2016 at 10:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.