Hi everyone,

I am taking the steps to upgrade from osticket v1.10.4 to 1.14.x series and I have noticed a huge performance drop, the default open ticket queue now takes about 15 seconds to load for a single user (testing it on my dev machine, on a snapshot of our production db ~ 30.000 tickets, 20gb db). The performance drop is ironic as I am currently running 1.10.4 on commodity hardware without the system ever breaking a sweat (congrats by the way), 10+ simultaneous users.

Under closer inspection memory footprint is about the same, its the cpu usage that spikes during this query:

SELECT A1.`number` AS `number`, A1.`ticket_id` AS `ticket_id`, A1.`source` AS `source`, A1.`lastupdate` AS `lastupdate`, B4.`subject` AS `cdata__subject`, A1.`isoverdue` AS `isoverdue`, B4.`priority` AS `cdata__priority`, B6.`firstname` AS `staff__firstname`, B6.`lastname` AS `staff__lastname`, B7.`name` AS `team__name`, A1.`team_id` AS `team_id`, A1.`isanswered` = '0' AS `2GkBZxz`, 

(SELECT COUNT(R0.`id`) AS `count` FROM `ost_thread` Q7 JOIN `ost_ticket` Q8 ON (Q7.`object_type` = 'T' AND Q7.`object_id` = Q8.`ticket_id`) LEFT JOIN `ost_thread_entry` R0 ON (Q7.`id` = R0.`thread_id`) WHERE Q8.`ticket_id` = A1.`ticket_id` AND NOT R0.`flags` & 4 != 0) AS _thread_count, 

(SELECT COUNT(R1.`id`) AS `count` FROM `ost_thread` Q7 JOIN `ost_ticket` Q8 ON (Q7.`object_type` = 'T' AND Q7.`object_id` = Q8.`ticket_id`) LEFT JOIN `ost_thread_entry` R0 ON (Q7.`id` = R0.`thread_id`) LEFT JOIN `ost_attachment` R1 ON (R1.`type` = 'H' AND R0.`id` = R1.`object_id`) WHERE Q8.`ticket_id` = A1.`ticket_id` AND R1.`inline` = 0) AS _att_count, 

B8.`expire` > NOW() AND NOT B8.`staff_id` = 1 AS `_locked`, 

(SELECT COUNT(R0.`id`) AS `count` FROM `ost_thread` Q7 JOIN `ost_ticket` Q8 ON (Q7.`object_type` = 'T' AND Q7.`object_id` = Q8.`ticket_id`) LEFT JOIN `ost_thread_collaborator` R0 ON (Q7.`id` = R0.`thread_id`) WHERE Q8.`ticket_id` = A1.`ticket_id`) AS _collabs, COALESCE(B6.`firstname`, B6.`lastname`, B7.`name`, 'zzz') AS `assignee` 

FROM `ost_ticket` A1 JOIN `ost_ticket_status` A2 ON (A1.`status_id` = A2.`id`) 

LEFT JOIN `ost_thread` A3 ON (A3.`object_type` = 'T' AND A1.`ticket_id` = A3.`object_id`) 
LEFT JOIN `ost_thread_referral` A4 ON (A3.`id` = A4.`thread_id`) 
LEFT JOIN `ost_staff` A5 ON (A4.`object_type` = 'S' AND A4.`object_id` = A5.`staff_id`) 
LEFT JOIN `ost_thread` A6 ON (A1.`ticket_id` = A6.`object_id` AND A6.`object_type` = 'C') 
LEFT JOIN `ost_thread_referral` A7 ON (A6.`id` = A7.`thread_id`) 
LEFT JOIN `ost_staff` A8 ON (A7.`object_type` = 'S' AND A7.`object_id` = A8.`staff_id`) 
LEFT JOIN `ost_team` B0 ON (A4.`object_type` = 'E' AND A4.`object_id` = B0.`team_id`) 
LEFT JOIN `ost_team` B1 ON (A7.`object_type` = 'E' AND A7.`object_id` = B1.`team_id`) 
LEFT JOIN `ost_department` B2 ON (A4.`object_type` = 'D' AND A4.`object_id` = B2.`id`) 
LEFT JOIN `ost_department` B3 ON (A7.`object_type` = 'D' AND A7.`object_id` = B3.`id`) 
LEFT JOIN `ost_ticket__cdata` B4 ON (A1.`ticket_id` = B4.`ticket_id`) 
LEFT JOIN `ost_ticket_priority` B5 ON (B4.`priority` = B5.`priority_id`) 
LEFT JOIN `ost_staff` B6 ON (A1.`staff_id` = B6.`staff_id`) 
LEFT JOIN `ost_team` B7 ON (A1.`team_id` = B7.`team_id`) 
LEFT JOIN `ost_lock` B8 ON (A1.`lock_id` = B8.`lock_id`)

WHERE A2.`state` = 'open' AND ((A2.`state` = 'open' AND (A1.`staff_id` = 1 OR A5.`staff_id` = 1 OR A6.`object_type` = 'C' AND A8.`staff_id` = 1 OR A1.`team_id` IN (1) OR B0.`team_id` IN (1) OR A6.`object_type` = 'C' AND B1.`team_id` IN (1))) OR A1.`dept_id` IN (1, 2, 3, 4, 10, 11, 12, 13, 14, 15, 16) OR B2.`id` IN (1, 2, 3, 4, 10, 11, 12, 13, 14, 15, 16) OR A6.`object_type` = 'C' AND B3.`id` IN (1, 2, 3, 4, 10, 11, 12, 13, 14, 15, 16)) AND A3.`object_type` = 'T' 

GROUP BY A1.`ticket_id` ORDER BY B5.`priority_urgency` ASC, A1.`lastupdate` DESC LIMIT 25;

25 rows in set (14.595 sec)

Running an explain statement for this query I understand that the slow query and hanging queues lies to the left join between ost_ticket and ost_ticket__cdata, the DBMS seems to not take advantage of the primary key in ost_ticket__cdata (ticket_id) or any index whatsoever.

+------+--------------------+-------+------------+--------------------------------------------+-----------------------+---------+-----------------------+---------+-----------------------------------------------------------+
| id   | select_type        | table | type       | possible_keys                              | key                   | key_len | ref                   | rows    | Extra                                                     |
+------+--------------------+-------+------------+--------------------------------------------+-----------------------+---------+-----------------------+---------+-----------------------------------------------------------+
|    1 | PRIMARY            | A2    | ref        | PRIMARY,state                              | state                 | 51      | const                 | 4       | Using where; Using index; Using temporary; Using filesort |
|    1 | PRIMARY            | A1    | ref        | PRIMARY,dept_id,staff_id,team_id,status_id | status_id             | 4       | osticket.A2.id        | 2684    | Using index condition                                     |
|    1 | PRIMARY            | A3    | ref|filter | object_id,object_type                      | object_id|object_type | 4|3     | osticket.A1.ticket_id | 1 (50%) | Using where; Using rowid filter                           |
|    1 | PRIMARY            | A4    | ref        | thread_id                                  | thread_id             | 4       | osticket.A3.id        | 1       |                                                           |
|    1 | PRIMARY            | A5    | eq_ref     | PRIMARY                                    | PRIMARY               | 4       | osticket.A4.object_id | 1       | Using where; Using index                                  |
|    1 | PRIMARY            | A6    | ref|filter | object_id,object_type                      | object_id|object_type | 4|3     | osticket.A1.ticket_id | 1 (0%)  | Using where; Using rowid filter                           |
|    1 | PRIMARY            | A7    | ref        | thread_id                                  | thread_id             | 4       | osticket.A6.id        | 1       | Using where                                               |
|    1 | PRIMARY            | A8    | eq_ref     | PRIMARY                                    | PRIMARY               | 4       | osticket.A7.object_id | 1       | Using where; Using index                                  |
|    1 | PRIMARY            | B0    | eq_ref     | PRIMARY                                    | PRIMARY               | 4       | osticket.A4.object_id | 1       | Using where; Using index                                  |
|    1 | PRIMARY            | B1    | eq_ref     | PRIMARY                                    | PRIMARY               | 4       | osticket.A7.object_id | 1       | Using where; Using index                                  |
|    1 | PRIMARY            | B2    | eq_ref     | PRIMARY                                    | PRIMARY               | 4       | osticket.A4.object_id | 1       | Using where; Using index                                  |
|    1 | PRIMARY            | B3    | eq_ref     | PRIMARY                                    | PRIMARY               | 4       | osticket.A7.object_id | 1       | Using where; Using index                                  |
|    1 | PRIMARY            | B4    | ALL        | PRIMARY                                    | NULL                  | NULL    | NULL                  | 26814   | Using where; Using join buffer (flat, BNL join)           |
|    1 | PRIMARY            | B5    | eq_ref     | PRIMARY                                    | PRIMARY               | 1       | osticket.B4.priority  | 1       | Using where                                               |
|    1 | PRIMARY            | B6    | eq_ref     | PRIMARY                                    | PRIMARY               | 4       | osticket.A1.staff_id  | 1       |                                                           |
|    1 | PRIMARY            | B7    | eq_ref     | PRIMARY                                    | PRIMARY               | 4       | osticket.A1.team_id   | 1       |                                                           |
|    1 | PRIMARY            | B8    | ALL        | PRIMARY                                    | NULL                  | NULL    | NULL                  | 1       | Using where; Using join buffer (flat, BNL join)           |
|    4 | DEPENDENT SUBQUERY | Q8    | eq_ref     | PRIMARY                                    | PRIMARY               | 4       | func                  | 1       | Using index                                               |
|    4 | DEPENDENT SUBQUERY | Q7    | ref        | object_id,object_type                      | object_id             | 4       | func                  | 1       | Using index condition; Using where                        |
|    4 | DEPENDENT SUBQUERY | R0    | ref        | collab                                     | collab                | 4       | osticket.Q7.id        | 1       | Using index                                               |
|    3 | DEPENDENT SUBQUERY | Q8    | eq_ref     | PRIMARY                                    | PRIMARY               | 4       | func                  | 1       | Using index                                               |
|    3 | DEPENDENT SUBQUERY | Q7    | ref        | PRIMARY,object_id,object_type              | object_id             | 4       | func                  | 1       | Using index condition; Using where                        |
|    3 | DEPENDENT SUBQUERY | R0    | ref        | PRIMARY,thread_id                          | thread_id             | 4       | osticket.Q7.id        | 1       | Using index                                               |
|    3 | DEPENDENT SUBQUERY | R1    | ref        | file-type                                  | file-type             | 4       | osticket.R0.id        | 1       | Using index condition; Using where                        |
|    2 | DEPENDENT SUBQUERY | Q8    | eq_ref     | PRIMARY                                    | PRIMARY               | 4       | func                  | 1       | Using index                                               |
|    2 | DEPENDENT SUBQUERY | Q7    | ref        | PRIMARY,object_id,object_type              | object_id             | 4       | func                  | 1       | Using index condition; Using where                        |
|    2 | DEPENDENT SUBQUERY | R0    | ref        | thread_id                                  | thread_id             | 4       | osticket.Q7.id        | 1       | Using where                                               |
+------+--------------------+-------+------------+--------------------------------------------+-----------------------+---------+-----------------------+---------+-----------------------------------------------------------+

After adding an optimizer hint to the initial query to force to use of the index performance is back as expected, with a massive improvement of 25 rows in set (0.081 sec).
I also tried to alter ost_ticket__cdata such that ticket_id has ost_ticket(ticket_id) as a foreign key but I dunno if that change had any impact whatsoever, probably not.

SELECT A1.`number` AS `number`, A1.`ticket_id` AS `ticket_id`, A1.`source` AS `source`, A1.`lastupdate` AS `lastupdate`, B4.`subject` AS `cdata__subject`, A1.`isoverdue` AS `isoverdue`, B4.`priority` AS `cdata__priority`, B6.`firstname` AS `staff__firstname`, B6.`lastname` AS `staff__lastname`, B7.`name` AS `team__name`, A1.`team_id` AS `team_id`, A1.`isanswered` = '0' AS `2GkBZxz`, 

(SELECT COUNT(R0.`id`) AS `count` FROM `ost_thread` Q7 JOIN `ost_ticket` Q8 ON (Q7.`object_type` = 'T' AND Q7.`object_id` = Q8.`ticket_id`) LEFT JOIN `ost_thread_entry` R0 ON (Q7.`id` = R0.`thread_id`) WHERE Q8.`ticket_id` = A1.`ticket_id` AND NOT R0.`flags` & 4 != 0) AS _thread_count, 

(SELECT COUNT(R1.`id`) AS `count` FROM `ost_thread` Q7 JOIN `ost_ticket` Q8 ON (Q7.`object_type` = 'T' AND Q7.`object_id` = Q8.`ticket_id`) LEFT JOIN `ost_thread_entry` R0 ON (Q7.`id` = R0.`thread_id`) LEFT JOIN `ost_attachment` R1 ON (R1.`type` = 'H' AND R0.`id` = R1.`object_id`) WHERE Q8.`ticket_id` = A1.`ticket_id` AND R1.`inline` = 0) AS _att_count, 

B8.`expire` > NOW() AND NOT B8.`staff_id` = 1 AS `_locked`, 

(SELECT COUNT(R0.`id`) AS `count` FROM `ost_thread` Q7 JOIN `ost_ticket` Q8 ON (Q7.`object_type` = 'T' AND Q7.`object_id` = Q8.`ticket_id`) LEFT JOIN `ost_thread_collaborator` R0 ON (Q7.`id` = R0.`thread_id`) WHERE Q8.`ticket_id` = A1.`ticket_id`) AS _collabs, COALESCE(B6.`firstname`, B6.`lastname`, B7.`name`, 'zzz') AS `assignee` 

FROM `ost_ticket` A1 JOIN `ost_ticket_status` A2 ON (A1.`status_id` = A2.`id`) 

LEFT JOIN `ost_thread` A3 ON (A3.`object_type` = 'T' AND A1.`ticket_id` = A3.`object_id`) 
LEFT JOIN `ost_thread_referral` A4 ON (A3.`id` = A4.`thread_id`) 
LEFT JOIN `ost_staff` A5 ON (A4.`object_type` = 'S' AND A4.`object_id` = A5.`staff_id`) 
LEFT JOIN `ost_thread` A6 ON (A1.`ticket_id` = A6.`object_id` AND A6.`object_type` = 'C') 
LEFT JOIN `ost_thread_referral` A7 ON (A6.`id` = A7.`thread_id`) 
LEFT JOIN `ost_staff` A8 ON (A7.`object_type` = 'S' AND A7.`object_id` = A8.`staff_id`) 
LEFT JOIN `ost_team` B0 ON (A4.`object_type` = 'E' AND A4.`object_id` = B0.`team_id`) 
LEFT JOIN `ost_team` B1 ON (A7.`object_type` = 'E' AND A7.`object_id` = B1.`team_id`) 
LEFT JOIN `ost_department` B2 ON (A4.`object_type` = 'D' AND A4.`object_id` = B2.`id`) 
LEFT JOIN `ost_department` B3 ON (A7.`object_type` = 'D' AND A7.`object_id` = B3.`id`) 
LEFT JOIN `ost_ticket__cdata` B4 force index for join (primary) ON (A1.`ticket_id` = B4.`ticket_id`) 
LEFT JOIN `ost_ticket_priority` B5 ON (B4.`priority` = B5.`priority_id`) 
LEFT JOIN `ost_staff` B6 ON (A1.`staff_id` = B6.`staff_id`) 
LEFT JOIN `ost_team` B7 ON (A1.`team_id` = B7.`team_id`) 
LEFT JOIN `ost_lock` B8 ON (A1.`lock_id` = B8.`lock_id`)

WHERE A2.`state` = 'open' AND ((A2.`state` = 'open' AND (A1.`staff_id` = 1 OR A5.`staff_id` = 1 OR A6.`object_type` = 'C' AND A8.`staff_id` = 1 OR A1.`team_id` IN (1) OR B0.`team_id` IN (1) OR A6.`object_type` = 'C' AND B1.`team_id` IN (1))) OR A1.`dept_id` IN (1, 2, 3, 4, 10, 11, 12, 13, 14, 15, 16) OR B2.`id` IN (1, 2, 3, 4, 10, 11, 12, 13, 14, 15, 16) OR A6.`object_type` = 'C' AND B3.`id` IN (1, 2, 3, 4, 10, 11, 12, 13, 14, 15, 16)) AND A3.`object_type` = 'T' 

GROUP BY A1.`ticket_id` ORDER BY B5.`priority_urgency` ASC, A1.`lastupdate` DESC LIMIT 25;

Updated DBMS plan after forced index use.

+------+--------------------+-------+------------+--------------------------------------------+-----------------------+---------+-----------------------+---------+-----------------------------------------------------------+
| id   | select_type        | table | type       | possible_keys                              | key                   | key_len | ref                   | rows    | Extra                                                     |
+------+--------------------+-------+------------+--------------------------------------------+-----------------------+---------+-----------------------+---------+-----------------------------------------------------------+
|    1 | PRIMARY            | A2    | ref        | PRIMARY,state                              | state                 | 51      | const                 | 4       | Using where; Using index; Using temporary; Using filesort |
|    1 | PRIMARY            | A1    | ref        | PRIMARY,dept_id,staff_id,team_id,status_id | status_id             | 4       | osticket.A2.id        | 2684    | Using index condition                                     |
|    1 | PRIMARY            | A3    | ref|filter | object_id,object_type                      | object_id|object_type | 4|3     | osticket.A1.ticket_id | 1 (50%) | Using where; Using rowid filter                           |
|    1 | PRIMARY            | A4    | ref        | thread_id                                  | thread_id             | 4       | osticket.A3.id        | 1       |                                                           |
|    1 | PRIMARY            | A5    | eq_ref     | PRIMARY                                    | PRIMARY               | 4       | osticket.A4.object_id | 1       | Using where; Using index                                  |
|    1 | PRIMARY            | A6    | ref|filter | object_id,object_type                      | object_id|object_type | 4|3     | osticket.A1.ticket_id | 1 (0%)  | Using where; Using rowid filter                           |
|    1 | PRIMARY            | A7    | ref        | thread_id                                  | thread_id             | 4       | osticket.A6.id        | 1       | Using where                                               |
|    1 | PRIMARY            | A8    | eq_ref     | PRIMARY                                    | PRIMARY               | 4       | osticket.A7.object_id | 1       | Using where; Using index                                  |
|    1 | PRIMARY            | B0    | eq_ref     | PRIMARY                                    | PRIMARY               | 4       | osticket.A4.object_id | 1       | Using where; Using index                                  |
|    1 | PRIMARY            | B1    | eq_ref     | PRIMARY                                    | PRIMARY               | 4       | osticket.A7.object_id | 1       | Using where; Using index                                  |
|    1 | PRIMARY            | B2    | eq_ref     | PRIMARY                                    | PRIMARY               | 4       | osticket.A4.object_id | 1       | Using where; Using index                                  |
|    1 | PRIMARY            | B3    | eq_ref     | PRIMARY                                    | PRIMARY               | 4       | osticket.A7.object_id | 1       | Using where; Using index                                  |
|    1 | PRIMARY            | B4    | eq_ref     | PRIMARY                                    | PRIMARY               | 4       | osticket.A1.ticket_id | 1       |                                                           |
|    1 | PRIMARY            | B5    | eq_ref     | PRIMARY                                    | PRIMARY               | 1       | osticket.B4.priority  | 1       | Using where                                               |
|    1 | PRIMARY            | B6    | ALL        | PRIMARY                                    | NULL                  | NULL    | NULL                  | 21      | Using where; Using join buffer (flat, BNL join)           |
|    1 | PRIMARY            | B7    | eq_ref     | PRIMARY                                    | PRIMARY               | 4       | osticket.A1.team_id   | 1       |                                                           |
|    1 | PRIMARY            | B8    | ALL        | PRIMARY                                    | NULL                  | NULL    | NULL                  | 1       | Using where; Using join buffer (flat, BNL join)           |
|    4 | DEPENDENT SUBQUERY | Q8    | eq_ref     | PRIMARY                                    | PRIMARY               | 4       | func                  | 1       | Using index                                               |
|    4 | DEPENDENT SUBQUERY | Q7    | ref        | object_id,object_type                      | object_id             | 4       | func                  | 1       | Using index condition; Using where                        |
|    4 | DEPENDENT SUBQUERY | R0    | ref        | collab                                     | collab                | 4       | osticket.Q7.id        | 1       | Using index                                               |
|    3 | DEPENDENT SUBQUERY | Q8    | eq_ref     | PRIMARY                                    | PRIMARY               | 4       | func                  | 1       | Using index                                               |
|    3 | DEPENDENT SUBQUERY | Q7    | ref        | PRIMARY,object_id,object_type              | object_id             | 4       | func                  | 1       | Using index condition; Using where                        |
|    3 | DEPENDENT SUBQUERY | R0    | ref        | PRIMARY,thread_id                          | thread_id             | 4       | osticket.Q7.id        | 1       | Using index                                               |
|    3 | DEPENDENT SUBQUERY | R1    | ref        | file-type                                  | file-type             | 4       | osticket.R0.id        | 1       | Using index condition; Using where                        |
|    2 | DEPENDENT SUBQUERY | Q8    | eq_ref     | PRIMARY                                    | PRIMARY               | 4       | func                  | 1       | Using index                                               |
|    2 | DEPENDENT SUBQUERY | Q7    | ref        | PRIMARY,object_id,object_type              | object_id             | 4       | func                  | 1       | Using index condition; Using where                        |
|    2 | DEPENDENT SUBQUERY | R0    | ref        | thread_id                                  | thread_id             | 4       | osticket.Q7.id        | 1       | Using where                                               |
+------+--------------------+-------+------------+--------------------------------------------+-----------------------+---------+-----------------------+---------+-----------------------------------------------------------+

Have other find this kind of severe performance drops? Is there a know fix? Is there a way to pass optimizer hints to the ORM? Is there an ORM overhaul on the works? We were really looking into upgrading our system, but I guess we will have to wait for a patch for this problem, security considered it's a big compromisse.