1

I have a slow query and I don't understand why.

This query takes 4 seconds for 45 results:

select * from `invoice` where `groupId` = 2602 and match (`search`) against ('+facture' in boolean mode);

Here is the explain:

+------+-------------+-------------+----------+----------------------------------+------------+---------+------+------+-------------+
| id   | select_type | table       | type     | possible_keys                    | key        | key_len | ref  | rows | Extra       |
+------+-------------+-------------+----------+----------------------------------+------------+---------+------+------+-------------+
|    1 | SIMPLE      | invoice | fulltext | invoice_groupid_index,searchFull | searchFull | 0       |      | 1    | Using where |
+------+-------------+-------------+----------+----------------------------------+------------+---------+------+------+-------------+

If I remove the condition "groupId = x", the query takes only 0.05 seconds for 43269 results.

select * from `invoice` where match (`search`) against ('+facture' in boolean mode);

Here is the explain:

+------+-------------+-------------+----------+---------------+------------+---------+------+------+-------------+
| id   | select_type | table       | type     | possible_keys | key        | key_len | ref  | rows | Extra       |
+------+-------------+-------------+----------+---------------+------------+---------+------+------+-------------+
|    1 | SIMPLE      | invoice | fulltext | searchFull    | searchFull | 0       |      | 1    | Using where |
+------+-------------+-------------+----------+---------------+------------+---------+------+------+-------------+

Do you have an idea why the first query is so slow?

Here is the table schema:

+---------+------------------+------+-----+---------+----------------+
| Field   | Type             | Null | Key | Default | Extra          |
+---------+------------------+------+-----+---------+----------------+
| id      | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| groupId | int(11)          | NO   | MUL | NULL    |                |
| search  | longtext         | YES  | MUL | NULL    |                |
+---------+------------------+------+-----+---------+----------------+

Thank you in advance for your help.

This question is different from Compound FULLTEXT index in MySQL because I can't create a composite key on "groupId" + "search", I got this error:

#1071 - Specified key was too long; max key length is 3072 bytes

Clément B.
  • 55
  • 1
  • 1
  • 7

0 Answers0