KevinTheJedi here is the output. Sorry, I did not understand anything, not good with core tech.
MariaDB [ostfm]> EXPLAIN SELECT A1.`number` AS `number`, A1.`source` AS `source`, A1.`lastupdate` AS `lastupdate`, B4.`subject` AS `cdata__subject`, A1.`ticket_id` AS `ticket_id`, A1.`isove
rdue` AS `isoverdue`, B5.`name` AS `user__name`, A1.`dept_id` AS `dept_id`, B6.`name` AS `dept__name`, 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`) L
EFT 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.`in
line` = 0) AS _att_count, B7.`expire` > NOW() AND NOT B7.`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 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_i
d`) LEFT JOIN `ost_user` B5 ON (A1.`user_id` = B5.`id`) LEFT JOIN `ost_department` B6 ON (A1.`dept_id` = B6.`id`) LEFT JOIN `ost_lock` B7 ON (A1.`lock_id` = B7.`lock_id`) WHERE A2.`state` =
'open' AND A2.`state` = 'open' AND A1.`isanswered` = '1' 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_i
d` 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, 4, 6, 14, 15, 17, 19, 20, 21, 22, 23, 26, 27) OR B2.`id` IN (1, 4, 6, 14, 15, 17,
19, 20, 21, 22, 23, 26, 27) OR A6.`object_type` = 'C' AND B3.`id` IN (1, 4, 6, 14, 15, 17, 19, 20, 21, 22, 23, 26, 27)) AND (A1.`ticket_pid` IS NULL OR A1.`flags` & 8 != 0) GROUP BY A1.`ticket_id` ORDER BY A1.`est_duedate` DESC LIMIT 30;
+------+--------------------+-------+------------+-----------------------------------------------+-----------------------+---------+--------------------+---------+-----------------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+--------------------+-------+------------+-----------------------------------------------+-----------------------+---------+--------------------+---------+-----------------------------------------------------------+
| 1 | PRIMARY | A2 | ref | PRIMARY,state | state | 51 | const | 1 | Using where; Using index; Using temporary; Using filesort |
| 1 | PRIMARY | A1 | ref | dept_id,staff_id,team_id,status_id,ticket_pid | status_id | 4 | ostfm.A2.id | 14727 | Using index condition; Using where |
| 1 | PRIMARY | A3 | ref|filter | object_id,object_type | object_id|object_type | 4|3 | ostfm.A1.ticket_id | 1 (50%) | Using where; Using rowid filter |
| 1 | PRIMARY | A4 | ref | thread_id | thread_id | 4 | ostfm.A3.id | 1 | Using where |
| 1 | PRIMARY | A5 | eq_ref | PRIMARY,group_id | PRIMARY | 4 | ostfm.A4.object_id | 1 | Using where; Using index |
| 1 | PRIMARY | A6 | ref|filter | object_id,object_type | object_id|object_type | 4|3 | ostfm.A1.ticket_id | 1 (0%) | Using where; Using rowid filter |
| 1 | PRIMARY | A7 | ref | thread_id | thread_id | 4 | ostfm.A6.id | 1 | Using where |
| 1 | PRIMARY | A8 | eq_ref | PRIMARY,group_id | PRIMARY | 4 | ostfm.A7.object_id | 1 | Using where; Using index |
| 1 | PRIMARY | B0 | eq_ref | PRIMARY | PRIMARY | 4 | ostfm.A4.object_id | 1 | Using where; Using index |
| 1 | PRIMARY | B1 | eq_ref | PRIMARY | PRIMARY | 4 | ostfm.A7.object_id | 1 | Using where; Using index |
| 1 | PRIMARY | B2 | eq_ref | PRIMARY | PRIMARY | 4 | ostfm.A4.object_id | 1 | Using where; Using index |
| 1 | PRIMARY | B3 | eq_ref | PRIMARY | PRIMARY | 4 | ostfm.A7.object_id | 1 | Using where; Using index |
| 1 | PRIMARY | B4 | ALL | PRIMARY | NULL | NULL | NULL | 35094 | Using where; Using join buffer (flat, BNL join) |
| 1 | PRIMARY | B5 | eq_ref | PRIMARY | PRIMARY | 4 | ostfm.A1.user_id | 1 | |
| 1 | PRIMARY | B6 | eq_ref | PRIMARY | PRIMARY | 4 | ostfm.A1.dept_id | 1 | |
| 1 | PRIMARY | B7 | 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 | ostfm.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 | ostfm.Q7.id | 1 | Using index |
| 3 | DEPENDENT SUBQUERY | R1 | ref | file-type | file-type | 4 | ostfm.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 | ostfm.Q7.id | 1 | Using where |
+------+--------------------+-------+------------+-----------------------------------------------+-----------------------+---------+--------------------+---------+-----------------------------------------------------------+
26 rows in set (0.010 sec)