@KevinTheJedi
I think I "may" have found the issue.
I logged into mysql & fired "SHOW FULL PROCESSLIST;"
The result is below query:
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.`isoverdue` 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`) 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, 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_id`) 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.`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, 4, 6, 27, 14, 15, 17, 19, 20, 21, 22, 23, 26) OR B2.`id` IN (1, 4, 6, 27, 14, 15, 17, 19, 20, 21, 22, 23, 26) OR A6.`object_type` = 'C' AND B3.`id` IN (1, 4, 6, 27, 14, 15, 17, 19, 20, 21, 22, 23, 26)) AND (A1.`ticket_pid` IS NULL OR A1.`flags` & 8 != 0) GROUP BY A1.`ticket_id` ORDER BY A1.`lastupdate` DESC LIMIT 30
This query fails because of incorrect table name (due to prefix).
The table names should be "ostjobs" but its "ost"
I manually edited the above query and replaced ost_ with ostjobs_ and executed the query and it worked fine.
Now, my question is does mysql cache queries?
May be mysql is reusing the query for the 2nd instance of OSTicket with the first instance.
Is my hypothesis correct?
Thanks