Mysql – Multi-Column Full Text Search Going Very Slow

full-text-searchMySQLoptimizationperformance

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

Best Answer

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 !!!

Related Solutions

Mysql – why/how does the number of matched columns influences the way of excecuting a query

I can provide with a general explanation, but it may not apply specifically to your particular case:

The way decision making works is by evaluation cost of execution plan, then picking up what is hopefully the cheapest plan. This you already know.

When it comes to indexing, though, stuff are getting interesting. The way to evaluate the usefulness or viability of an index is to estimate the selectivity given some value.

For the moment, forget about your FULLTEXT index, and let's assume a simple index on some column col1, and another index on some column col2. Given the following two queries:

SELECT * FROM t WHERE col1 < 10 and col2 = 4;
SELECT * FROM t WHERE col1 BETWEEN 100 AND 110 and col2 = 4;

It may happen that the query is evaluated differently in these two cases. Why? Because it may happen that col2 = 4 returns more rows than col1 < 10, in which case we prefer to use index on col1. But then, it may return less rows than col1 BETWEEN 100 AND 110, in which case we prefer the index on col2.

Your case is not very much different. MySQL estimates the number of rows returned by some index query. When you use more columns, MySQL gets the impression your index is likely to result with few rows. So it chooses to start with TableA, then joins what should be very few rows with TableB.

But if MySQL believes the index to return many rows, it may prefer starting with TableB. Why is that? Because you are sorting on indexed columns of TableB. Sorting is a lot of work, too. So MySQL may choose to first sort the rows, then join to TableA and filter by fulltext index. It may not be a bad idea if the fulltext search yields with many rows anyhow.

Thesql match against boolean mode

You may need to try some wildcard stuff

MATCH (field) AGAINST ('+85604*' IN BOOLEAN MODE)
MATCH (field) AGAINST ('+8560*' IN BOOLEAN MODE)

The MySQL Documentation says on '*':

*

The asterisk serves as the truncation (or wildcard) operator. Unlike the other operators, it is appended to the word to be affected. Words match if they begin with the word preceding the * operator.

If a word is specified with the truncation operator, it is not stripped from a boolean query, even if it is too short or a stopword. Whether a word is too short is determined from the innodb_ft_min_token_size setting for InnoDB tables, or ft_min_word_len for MyISAM tables. The wildcarded word is considered as a prefix that must be present at the start of one or more words. If the minimum word length is 4, a search for '+word +the*' could return fewer rows than a search for '+word +the', because the second query ignores the too-short search term the.

Here is an example from the Documentation:

'apple*'

Find rows that contain words such as “apple”, “apples”, “applesauce”, or “applet”.

For more information, see MySQL Documentation on Fine Tuning Your Searches

Best Answer

PROBLEM

SOLUTION

GIVE IT A TRY !!!

Related Solutions

Mysql – why/how does the number of matched columns influences the way of excecuting a query

Thesql match against boolean mode

Related Question