Sample Header Ad - 728x90

MySQL FULLTEXT Match exponentially slower with search phrase

0 votes
1 answer
1542 views
I'm trying to track down some performance issues in my FULLTEXT MATCH() AGAINST() query, and it seems to be related (at least in part) to queries requiring an exact phrase. My table stores system process information, has roughly 2M rows, a keywords TEXT column, and a FULLTEXT index on said column. The keywords column averages ~600 characters, with several "words" in that column containing one or many special characters. The query I'm trying to run is an exact match on "net.exe": `select id from processes where id " > > A phrase that is enclosed within double quote (") characters matches > only rows that contain the phrase literally, as it was typed. The > full-text engine splits the phrase into words and performs a search in > the FULLTEXT index for the words. Nonword characters need not be > matched exactly: Phrase searching requires only that matches contain > exactly the same words as the phrase and in the same order. For > example, "test phrase" matches "test, phrase". ...and indeed, running queries with ... AGAINST('"net exe"' ... take just as long. So it seems to just be searching for exact phrases in general. My latest theory is that because my table has process info (e.g. system paths and cmdline arguments, which have many special characters), the normal FULLTEXT isn't useful for my query and MySQL is effectively re-indexing the whole table when I search for phrases like "net.exe". Some supporting evidence for this is that the original creation of the FULLTEXT index took roughly 30 minutes. However I find it hard to believe that would be the full explanation. Regardless, I ran explain on my query (which itself took 30 minutes to resolve), and got the following: ``` mysql> explain select id from processes where id explain select id from processes where id Ft_hints: sorted, which seems to only be due to the lack of quotes. If I run explain when querying for "net" it goes back to Ft_hints: no_ranking. Lastly, I tried running CHECK TABLE and even making a fresh temp table with only the id and keywords columns, but the above numbers were consistent, so I don't feel this is specific to my table condition.
Asked by Eric (103 rep)
Oct 4, 2022, 11:51 PM
Last activity: Aug 5, 2025, 11:03 AM