3

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:

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 '14 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 '14 at 16:34
  • @Stoleg, I updated the question. – Jeremy Boyd Aug 26 '14 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 '14 at 18:34
  • Screenshotted "Show Indexes from articles". My query is in the proper order. – Jeremy Boyd Aug 26 '14 at 19:04
6

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

GIVE IT A TRY !!!

  • That worked like a charm. Can't believe it took 2 days for that ;). – Jeremy Boyd Aug 26 '14 at 21:17
  • I am sorry I took so long to get to it. – RolandoMySQLDBA Aug 26 '14 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 '16 at 10:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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