0

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.

1 Answer 1

0

In some situations, the best solution is to start with a FULLTEXT MATCH that will be efficient, but get too many rows. Then narrow the query down. Try this:

WHERE MATCH(keywords) AGAINST('+net +exe' IN BOOLEAN MODE)
  AND keywords LIKE '%net.exe%'

On another note: If too high a percentage of rows have, say, 'exe' in them, that 'word' won't be indexed.

3
  • Thanks - that's a good hybrid to use in case I can't get the MATCH working directly. Regarding the indexing, I knew stopwords could be altered, but are they also automatically set like that? Do you have any documentation I can reference as to what the limit may be? 'exe' would certainly be referenced a /lot/ in a processes databases...
    – Eric
    Oct 5, 2022 at 2:24
  • Figured I'd test to be sure, running a match against "exe" runs in a handful of seconds and finds results like the other simple queries, so at least that's seemingly not the issue here
    – Eric
    Oct 5, 2022 at 2:26
  • @Eric - The limits for MyISAM's Fulltext were clearly stated. Alas, not so for InnoDB (that I have found yet).
    – Rick James
    Oct 5, 2022 at 3:48

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.