4

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.

0

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.

0

For an exact match, use '=', not FULLTEXT:

INDEX(file_path) -- in addition to `FULLTEXT(file_path)`

WHERE file_path = "c:\\data\\clients"  -- instead of MATCH...

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.