Performance problem using mysql fulltext search I have a fulltext index on a column that contains an absolute file path (file_path), for a file indexing application.

There are 500 000 rows in the table. The MySQL version is 5.7.19. 

Most, if not all rows begins with 'c:\data\' (ie: 'c:\data\clients\microsoft\private\emails.xls')

When I perform a search for 'microsoft private emails', ie; 

MATCH(file_path) AGAINST('microsoft private emails' IN BOOLEAN MODE)`


everything is fine and I get the results in less than a second.

When I perform a search with the root of the file path, ie; 

MATCH(file_path) AGAINST('"c:\\data\\clients"' IN BOOLEAN MODE) 
-- note that it's an exact match with quotes ""


... the query runs forever (443 seconds last time I waited long enough), and the CPU hits 100% during the time of the query. 

I use a LIMIT 0, 10 on both queries. The problem happens both with MyISAM and Innodb.

In fact, I have to execute $ service mysql restart to get things back to normal, otherwise the whole server becomes more-or-less responsive.

My question is: 

Why is this happening and is there a way to fix it?

My theory, and I have very little knowledge about how the fulltext index works, is the fact that c:\data is in every row, the index fails and it's somehow doing a full table scan but with so many '\' in every row, it causes mysql to just loose it.

If I am right about the '\' thing, how is it possible to search for absolute file paths with mysql's full text index?

I hope I provided enough details.
Solved it by using MariaDB 10.2

MariaDB reduced the query execution time from 300 seconds to 30 seconds.

I then profiled and optimized the query down to 0.156sec.