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.
