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