Hi everyone, I recently migrated my osTicket installation from Ubuntu 14.x/PHP 7.1/mysql 5.5.x to a brand new Ubuntu 20.04/PHP 7.4.3/mysql 8.0.21. Hardware specs of both server are the same.

Everything went smoothly and server is up and running with all the previous data.
HOWEVER, certain sql queries that use a lot of joins [1] and are generated by osTicket UI seem to take around 4x longer to execute. For example to see all closed tickets takes around 12 sec, where the SAME sql query in the same dataset in the old mysql server took around 3 sec.

Running analyze query in both old/new mysql servers indicate that different number of rows is produced in respective steps in each case, so this probably means that newer mysql version interprets and executes the SQL query differently.
See the highlighed lines:

mysql 8.0.x

mysql 5.x

mysqltuner doesn't see anything really wrong with my current settings, however I tried modifying some mysql parameters (like join_buffer_size -tried values up to 300M !), but got no real difference.

So, have you experienced such an issue in your installation? I'm running the current latest version (v1.14.3)
What can be done to boost sql performance to previous levels? For example I'm thinking of moving to MariaDB OR MySQL 5.7.x

Thanks in advance for your time!
[1] For example:

SELECT A1.number AS number, A1.ticket_id AS ticket_id, A1.source AS source, A1.closed AS closed, B4.subject AS cdata__subject, A1.isoverdue AS isoverdue, B5.name AS user__name, 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 = 3 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_user B5 ON (A1.user_id = B5.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 = 'closed' AND ((A2.state = 'open' AND (A1.staff_id = 3 OR A5.staff_id = 3 OR A6.object_type = 'C' AND A8.staff_id = 3 OR A1.team_id IN (4) OR B0.team_id IN (4) OR A6.object_type = 'C' AND B1.team_id IN (4))) OR A1.dept_id IN (1, 4) OR B2.id IN (1, 4) OR A6.object_type = 'C' AND B3.id IN (1, 4)) AND (A1.ticket_pid IS NULL OR A1.flags & 8 != 0) GROUP BY A1.ticket_id ORDER BY A1.closed DESC LIMIT 30