Rt with mysql 5.7 is very slow

RT Users
1

I’m using rt 4.4.2, about 2 mln tickets, about 10 mln attachments.

Was using it with mysql 5.6 and sphinx for fulltext searching. Now migrated to mysql 5.7 and builtin fulltext search.

Unfortunately the problem is that mysql fulltext search is way slower than sphinx. Waaaay slower.

Simple “test” entered in upper-right search box in rt makes rt do a sql query which takes 14 minutes to finish (below) and that’s not all, it only counts tickets.

Is such behaviour expected and what can be improved?

11:13:44 mysql{17}> explain SELECT COUNT(DISTINCT main.id) FROM Tickets main JOIN Queues Queues_4  ON ( Queues_4.id = main.Queue ) JOIN Transactions Transactions_1  ON ( Transactions_1.ObjectType = 'RT::Ticket' ) AND ( Transactions_1.ObjectId = main.id ) JOIN Attachments Attachments_2  ON ( Attachments_2.TransactionId = Transactions_1.id ) JOIN AttachmentsIndex AttachmentsIndex_3  ON ( AttachmentsIndex_3.id = Attachments_2.id )  WHERE (main.IsMerged IS NULL) AND (main.Status != 'deleted') AND (main.Type = 'ticket') AND ( (  ( MATCH(AttachmentsIndex_3.Content) AGAINST ('test' IN BOOLEAN MODE) AND AttachmentsIndex_3.Content IS NOT NULL )  )  AND  (  (  ( Queues_4.Lifecycle = 'assets' AND  ( main.Status = 'new' OR main.Status = 'allocated' OR main.Status = 'in-use' )  )  OR  ( Queues_4.Lifecycle = 'default' AND  ( main.Status = 'new' OR main.Status = 'open' OR main.Status = 'stalled' )  )  OR  ( Queues_4.Lifecycle = 'approvals' AND  ( main.Status = 'new' OR main.Status = 'open' OR main.Status = 'stalled' )  )  )  ) );
+----+-------------+--------------------+------------+----------+---------------------------------+------------------+---------+---------------------------------+------+----------+-----------------------------------+
| id | select_type | table              | partitions | type     | possible_keys                   | key              | key_len | ref                             | rows | filtered | Extra                             |
+----+-------------+--------------------+------------+----------+---------------------------------+------------------+---------+---------------------------------+------+----------+-----------------------------------+
|  1 | SIMPLE      | AttachmentsIndex_3 | NULL       | fulltext | PRIMARY,AttachmentsIndex        | AttachmentsIndex | 0       | const                           |    1 |   100.00 | Using where; Ft_hints: no_ranking |
|  1 | SIMPLE      | Attachments_2      | NULL       | eq_ref   | PRIMARY,Attachments2            | PRIMARY          | 4       | rt3.AttachmentsIndex_3.id       |    1 |   100.00 | Using where                       |
|  1 | SIMPLE      | Transactions_1     | NULL       | eq_ref   | PRIMARY,Transactions1           | PRIMARY          | 4       | rt3.Attachments_2.TransactionId |    1 |    50.00 | Using where                       |
|  1 | SIMPLE      | main               | NULL       | eq_ref   | PRIMARY,Tickets1,tickets_status | PRIMARY          | 4       | rt3.Transactions_1.ObjectId     |    1 |     5.00 | Using where                       |
|  1 | SIMPLE      | Queues_4           | NULL       | eq_ref   | PRIMARY                         | PRIMARY          | 4       | rt3.main.Queue                  |    1 |    27.10 | Using where                       |
+----+-------------+--------------------+------------+----------+---------------------------------+------------------+---------+---------------------------------+------+----------+-----------------------------------+
5 rows in set, 1 warning (0.07 sec)

Note (Code 1003): /* select#1 */ select count(distinct `rt3`.`main`.`id`) AS `COUNT(DISTINCT main.id)` from `rt3`.`Tickets` `main` join `rt3`.`Queues` `Queues_4` join `rt3`.`Transactions` `Transactions_1` join `rt3`.`Attachments` `Attachments_2` join `rt3`.`AttachmentsIndex` `AttachmentsIndex_3` where ((`rt3`.`Queues_4`.`id` = `rt3`.`main`.`Queue`) and (`rt3`.`main`.`id` = `rt3`.`Transactions_1`.`ObjectId`) and (`rt3`.`Transactions_1`.`ObjectType` = 'RT::Ticket') and (`rt3`.`Transactions_1`.`id` = `rt3`.`Attachments_2`.`TransactionId`) and (`rt3`.`main`.`Type` = 'ticket') and isnull(`rt3`.`main`.`IsMerged`) and (`rt3`.`main`.`Status` <> 'deleted') and (match `rt3`.`AttachmentsIndex_3`.`Content` against ('test' in boolean mode)) and (`rt3`.`AttachmentsIndex_3`.`Content` is not null) and (((`rt3`.`Queues_4`.`Lifecycle` = 'assets') and ((`rt3`.`main`.`Status` = 'new') or (`rt3`.`main`.`Status` = 'allocated') or (`rt3`.`main`.`Status` = 'in-use'))) or ((`rt3`.`Queues_4`.`Lifecycle` = 'default') and ((`rt3`.`main`.`Status` = 'new') or (`rt3`.`main`.`Status` = 'open') or (`rt3`.`main`.`Status` = 'stalled'))) or ((`rt3`.`Queues_4`.`Lifecycle` = 'approvals') and ((`rt3`.`main`.`Status` = 'new') or (`rt3`.`main`.`Status` = 'open') or (`rt3`.`main`.`Status` = 'stalled')))) and (`rt3`.`AttachmentsIndex_3`.`id` = `rt3`.`Attachments_2`.`id`))
11:13:45 mysql{18}> SELECT COUNT(DISTINCT main.id) FROM Tickets main JOIN Queues Queues_4  ON ( Queues_4.id = main.Queue ) JOIN Transactions Transactions_1  ON ( Transactions_1.ObjectType = 'RT::Ticket' ) AND ( Transactions_1.ObjectId = main.id ) JOIN Attachments Attachments_2  ON ( Attachments_2.TransactionId = Transactions_1.id ) JOIN AttachmentsIndex AttachmentsIndex_3  ON ( AttachmentsIndex_3.id = Attachments_2.id )  WHERE (main.IsMerged IS NULL) AND (main.Status != 'deleted') AND (main.Type = 'ticket') AND ( (  ( MATCH(AttachmentsIndex_3.Content) AGAINST ('test' IN BOOLEAN MODE) AND AttachmentsIndex_3.Content IS NOT NULL )  )  AND  (  (  ( Queues_4.Lifecycle = 'assets' AND  ( main.Status = 'new' OR main.Status = 'allocated' OR main.Status = 'in-use' )  )  OR  ( Queues_4.Lifecycle = 'default' AND  ( main.Status = 'new' OR main.Status = 'open' OR main.Status = 'stalled' )  )  OR  ( Queues_4.Lifecycle = 'approvals' AND  ( main.Status = 'new' OR main.Status = 'open' OR main.Status = 'stalled' )  )  )  ) );
+-------------------------+
| COUNT(DISTINCT main.id) |
+-------------------------+
|                      71 |
+-------------------------+
1 row in set (14 min 11.72 sec)

11:28:04 mysql{19}> show create table AttachmentsIndex;
±-----------------±------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
±-----------------±------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| AttachmentsIndex | CREATE TABLE AttachmentsIndex (
id int(10) unsigned NOT NULL AUTO_INCREMENT,
Content longtext,
PRIMARY KEY (id),
FULLTEXT KEY AttachmentsIndex (Content)
) ENGINE=InnoDB AUTO_INCREMENT=10932372 DEFAULT CHARSET=utf8 |
±-----------------±------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

Now the fun part. It doesn’t seem fulltext part to be the issue because

11:37:52 mysql{37}> select count(DISTINCT id) FROM AttachmentsIndex WHERE match(AttachmentsIndex.`Content`) against ('test' in boolean mode);
+--------------------+
| count(DISTINCT id) |
+--------------------+
|              44114 |
+--------------------+
1 row in set (0.00 sec)

which is fast, so the problem lies in the rest of query.

2

SELECT DISTINCT main.* FROM Users main CROSS JOIN ACL ACL_3 JOIN Principals Principals_1 ON ( Principals_1.id = main.id ) JOIN CachedGroupMembers CachedGroupMembers_2 ON ( CachedGroupMembers_2.MemberId = Principals_1.id ) JOIN CachedGroupMembers CachedGroupMembers_4 ON ( CachedGroupMembers_4.MemberId = Principals_1.id ) WHERE ((ACL_3.ObjectType = 'RT::Queue') OR (ACL_3.ObjectType = 'RT::System' AND ACL_3.ObjectId = 1)) AND (ACL_3.PrincipalId = CachedGroupMembers_4.GroupId) AND (ACL_3.PrincipalType = 'Group') AND (ACL_3.RightName = 'OwnTicket') AND (CachedGroupMembers_2.Disabled = '0') AND (CachedGroupMembers_2.GroupId = '4') AND (CachedGroupMembers_4.Disabled = '0') AND (Principals_1.Disabled = '0') AND (Principals_1.PrincipalType = 'User') AND (Principals_1.id != '1') ORDER BY main.Name ASC

This query is very slow and doesn’t use indexes:

13:08:42 mysql{123}> explain SELECT DISTINCT main.* FROM Users main CROSS JOIN ACL ACL_3 JOIN Principals Principals_1  ON ( Principals_1.id = main.id ) JOIN CachedGroupMembers CachedGroupMembers_2  ON ( CachedGroupMembers_2.MemberId = Principals_1.id ) JOIN CachedGroupMembers CachedGroupMembers_4  ON ( CachedGroupMembers_4.MemberId = Principals_1.id )  WHERE ((ACL_3.ObjectType = 'RT::Queue') OR (ACL_3.ObjectType = 'RT::System' AND ACL_3.ObjectId   = 1)) AND (ACL_3.PrincipalId = CachedGroupMembers_4.GroupId) AND (ACL_3.PrincipalType = 'Group') AND (ACL_3.RightName = 'OwnTicket') AND (CachedGroupMembers_2.Disabled = '0') AND (CachedGroupMembers_2.GroupId = '4') AND (CachedGroupMembers_4.Disabled = '0') AND (Principals_1.Disabled = '0') AND (Principals_1.PrincipalType = 'User') AND (Principals_1.id != '1')  ORDER BY main.Name ASC;
+----+-------------+----------------------+------------+--------+----------------------------------------------------+---------------------+---------+-----------------------------------+------+----------+---------------------------------------------------------------------------+
| id | select_type | table                | partitions | type   | possible_keys                                      | key                 | key_len | ref                               | rows | filtered | Extra                                                                     |
+----+-------------+----------------------+------------+--------+----------------------------------------------------+---------------------+---------+-----------------------------------+------+----------+---------------------------------------------------------------------------+
|  1 | SIMPLE      | CachedGroupMembers_2 | NULL       | range  | DisGrouMem,CachedGroupMembers3,cachedgroupmembers1 | DisGrouMem          | 10      | NULL                              |   28 |    10.00 | Using where; Using index; Using temporary; Using filesort                 |
|  1 | SIMPLE      | CachedGroupMembers_4 | NULL       | ref    | DisGrouMem,CachedGroupMembers3,cachedgroupmembers1 | cachedgroupmembers1 | 5       | rt3.CachedGroupMembers_2.MemberId |    1 |    10.00 | Using where; Using index                                                  |
|  1 | SIMPLE      | main                 | NULL       | eq_ref | PRIMARY                                            | PRIMARY             | 4       | rt3.CachedGroupMembers_2.MemberId |    1 |   100.00 | NULL                                                                      |
|  1 | SIMPLE      | Principals_1         | NULL       | eq_ref | PRIMARY                                            | PRIMARY             | 4       | rt3.CachedGroupMembers_2.MemberId |    1 |     5.00 | Using where; Distinct                                                     |
|  1 | SIMPLE      | ACL_3                | NULL       | range  | ACL1,ACL_PrincipalId                               | ACL1                | 85      | NULL                              |   86 |    10.00 | Using where; Using index; Distinct; Using join buffer (Block Nested Loop) |
+----+-------------+----------------------+------------+--------+----------------------------------------------------+---------------------+---------+-----------------------------------+------+----------+---------------------------------------------------------------------------+
5 rows in set, 1 warning (0.00 sec)

Does it use indexes for anyone else? Run above explain as a test.

3

Added
CREATE INDEX Grou ON CachedGroupMembers(GroupId);
CREATE INDEX Dis ON CachedGroupMembers(Disabled);

and now new indexes are being used:

10:29:41 mysql{81}> explain extended SELECT DISTINCT main.* FROM Users main CROSS JOIN ACL ACL_3 JOIN Principals Principals_1  ON ( Principals_1.id = main.id ) JOIN CachedGroupMembers CachedGroupMembers_2  ON ( CachedGroupMembers_2.MemberId = Principals_1.id ) JOIN CachedGroupMembers CachedGroupMembers_4  ON ( CachedGroupMembers_4.MemberId = Principals_1.id )  WHERE ((ACL_3.ObjectType = 'RT::Queue') OR (ACL_3.ObjectType = 'RT::System' AND ACL_3.ObjectId   = 1)) AND (ACL_3.PrincipalId = CachedGroupMembers_4.GroupId) AND (ACL_3.PrincipalType = 'Group') AND (ACL_3.RightName = 'OwnTicket') AND (CachedGroupMembers_2.Disabled = '0') AND (CachedGroupMembers_2.GroupId = '4') AND (CachedGroupMembers_4.Disabled = '0') AND (Principals_1.Disabled = '0') AND (Principals_1.PrincipalType = 'User') AND (Principals_1.id != '1')  ORDER BY main.Name ASC;
+----+-------------+----------------------+------------+--------+-------------------------------------------------------------+------------+---------+-----------------------------------------------+------+----------+-----------------------------------------------------------+
| id | select_type | table                | partitions | type   | possible_keys                                               | key        | key_len | ref                                           | rows | filtered | Extra                                                     |
+----+-------------+----------------------+------------+--------+-------------------------------------------------------------+------------+---------+-----------------------------------------------+------+----------+-----------------------------------------------------------+
|  1 | SIMPLE      | ACL_3                | NULL       | range  | ACL1,ACL_PrincipalId                                        | ACL1       | 85      | NULL                                          |   86 |   100.00 | Using where; Using index; Using temporary; Using filesort |
|  1 | SIMPLE      | CachedGroupMembers_4 | NULL       | ref    | DisGrouMem,CachedGroupMembers3,cachedgroupmembers1,Dis,Grou | Grou       | 5       | rt3.ACL_3.PrincipalId                         |    1 |    25.00 | Using where                                               |
|  1 | SIMPLE      | CachedGroupMembers_2 | NULL       | ref    | DisGrouMem,CachedGroupMembers3,cachedgroupmembers1,Dis,Grou | DisGrouMem | 12      | const,rt3.CachedGroupMembers_4.MemberId,const |    1 |   100.00 | Using index                                               |
|  1 | SIMPLE      | main                 | NULL       | eq_ref | PRIMARY                                                     | PRIMARY    | 4       | rt3.CachedGroupMembers_4.MemberId             |    1 |   100.00 | NULL                                                      |
|  1 | SIMPLE      | Principals_1         | NULL       | eq_ref | PRIMARY                                                     | PRIMARY    | 4       | rt3.CachedGroupMembers_4.MemberId             |    1 |     5.00 | Using where; Distinct                                     |
+----+-------------+----------------------+------------+--------+-------------------------------------------------------------+------------+---------+-----------------------------------------------+------+----------+-----------------------------------------------------------+
5 rows in set, 2 warnings (0.00 sec)

For me rt is fast enough with that change. First index helped a lot.