mysql match() agains() OR match() against() SLOW

I have table with quite many rows (~2M).
When i search it like

SELECT * FROM product WHERE 
MATCH(name) AGAINST('+some' '+word' IN BOOLEAN MODE)

it works like charm and finds what i need in less than 0.5s.

But when i search for 2 sets of words like this

SELECT * FROM product WHERE 
MATCH(name) AGAINST('+some' '-word' IN BOOLEAN MODE) 
OR 
MATCH(name) AGAINST('+something' '-other' IN BOOLEAN MODE)

search takes sometimes over minute.

I would expect it to work 2 times slower (it’s 2 searches), maybe a bit more (you still have to compare results and remove duplicates, but if there are only few results it should not take long), but not so much longer. After adding OR it works slower, than LIKE “%…%” OR LIKE “%…%”

Anyone can tell me what am i doing wrong?

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

Unfortunately for you, fulltext indexes have some limitations, and not being able to properly merge the results of two independent fulltext searches is one them:

The Index Merge optimization algorithm has the following known limitations:

• […]
• Index Merge is not applicable to full-text indexes.

Fortunately for you, fulltext searches can be more complex, so you can merge your searches yourself. Your second query can be written as a single search using:

SELECT * FROM product WHERE 
MATCH(name) AGAINST('(+something -other) (+some -word)' IN BOOLEAN MODE)

This defines two search sets and is ok if either of the two (...) matches – which is an or.

Alternatively, you can use a union instead of an or, which allows MySQL to actually run two independent fulltext searches and then combine the two results, which is basically what you are thinking of:

SELECT * FROM product WHERE 
MATCH(name) AGAINST('+some -word' IN BOOLEAN MODE) 
UNION 
SELECT * FROM product WHERE 
MATCH(name) AGAINST('+something -other' IN BOOLEAN MODE)

This also works for more complicated situations, e.g. merging searches on different columns, but will not work that easy if you want to do something else than or.

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0