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 < 10000 AND MATCH(keywords) AGAINST('"net.exe"' IN BOOLEAN MODE);
...the id < 10000 in this case is just to limit the results.
As written, this query takes roughly 30 minutes to complete. However running simpler queries net results in seconds. For example:
... AGAINST('+net' ... took 5.6s
... AGAINST('+net +exe' ... took 7.1s
... AGAINST('net.exe' ... took 11.3s (but is treated the same as 'net exe' without the quotes)
... AGAINST('"net"' ... took 6.7s
Profiling shows a pretty consistent 1.7s per word goes to FULLTEXT initialization when running without a phrase, and the rest generally goes to executing, but when running the original query the FULLTEXT initialization is through the roof:
+--------------------------------+------------+
| Status | Duration |
+--------------------------------+------------+
| starting | 0.000117 |
| Executing hook on transaction | 0.000010 |
| starting | 0.000010 |
| checking permissions | 0.000009 |
| Opening tables | 0.000066 |
| init | 0.000010 |
| System lock | 0.000015 |
| optimizing | 0.000024 |
| statistics | 0.000168 |
| preparing | 0.000014 |
| FULLTEXT initialization | 999.999999 |
| executing | 2.770143 |
| end | 0.000013 |
| query end | 0.000006 |
| waiting for handler commit | 0.000032 |
| closing tables | 0.000052 |
| freeing items | 0.122969 |
| cleaning up | 0.000047 |
+--------------------------------+------------+
Why would requiring the exact string "net.exe" be taking so much longer? Note: I realize I could just use LIKE here instead, and may have to, but would like to benefit from the Boolean Search Operators if possible. Originally I thought it had something to do with the fullstop . character, since there are several other special characters in my table, but according to the MySQL documentation it should still be ignored and functionally equivalent as "net exe":
"
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 < 10000 AND MATCH(keywords) AGAINST('"net.exe"');
+----+-------------+-----------+------------+----------+----------------------------+--------------+---------+-------+------+----------+-----------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-----------+------------+----------+----------------------------+--------------+---------+-------+------+----------+-----------------------------------+
| 1 | SIMPLE | processes | NULL | fulltext | idx_timestamp,idx_keywords | idx_keywords | 0 | const | 1 | 5.00 | Using where; Ft_hints: no_ranking |
+----+-------------+-----------+------------+----------+----------------------------+--------------+---------+-------+------+----------+-----------------------------------+
1 row in set, 1 warning (31 min 20.32 sec)
For context, here is explain on one of the simple queries:
mysql> explain select id from processes where id < 10000 AND MATCH(keywords) AGAINST('net');
+----+-------------+-----------+------------+----------+----------------------------+--------------+---------+-------+------+----------+-------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-----------+------------+----------+----------------------------+--------------+---------+-------+------+----------+-------------------------------+
| 1 | SIMPLE | processes | NULL | fulltext | idx_timestamp,idx_keywords | idx_keywords | 0 | const | 1 | 5.00 | Using where; Ft_hints: sorted |
+----+-------------+-----------+------------+----------+----------------------------+--------------+---------+-------+------+----------+-------------------------------+
1 row in set, 1 warning (1.22 sec)
The only difference is Ft_hints: no_ranking > 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.
