Something else, the db admin gived me this query as the one wich gives the problem (i restored the failing DB and tryed to login and the loop comed back again). they finded this manually and gived me this:

SELECT COUNT(DISTINCT CASE WHEN B3.address IS NOT NULL AND A3.object_type = 'T' THEN A1.ticket_id END) AS q15, COUNT(DISTINCT CASE WHEN B3.address IS NOT NULL AND B3.address IS NOT NULL AND A1.dept_id = 4 AND A3.object_type = 'T' THEN A1.ticket_id END) AS q16, COUNT(DISTINCT CASE WHEN B3.address IS NOT NULL AND A1.dept_id = 4 AND B3.address IS NOT NULL AND A1.dept_id = 4 AND B5.value IS NOT NULL AND A3.object_type = 'T' THEN A1.ticket_id END) AS q24, COUNT(DISTINCT CASE WHEN B3.address IS NOT NULL AND A1.dept_id = 4 AND B5.value IS NOT NULL AND B3.address IS NOT NULL AND A1.dept_id = 4 AND B5.value IS NOT NULL AND B5.value REGEXP '\"?81(\"|,|$)' AND A3.object_type = 'T' THEN A1.ticket_id END) AS q25, COUNT(DISTINCT CASE WHEN B3.address IS NOT NULL AND A1.dept_id = 4 AND B5.value IS NOT NULL AND B3.address IS NOT NULL AND A1.dept_id = 4 AND B5.value IS NOT NULL AND B5.value REGEXP '\"?82(\"|,|$)' AND A3.object_type = 'T' THEN A1.ticket_id END) AS q26, COUNT(DISTINCT CASE WHEN B3.address IS NOT NULL AND A1.dept_id = 4 AND B5.value IS NOT NULL AND B3.address IS NOT NULL AND A1.dept_id = 4 AND B5.value IS NOT NULL AND B5.value REGEXP '\"?83(\"|,|$)|\"?84(\"|,|$)' AND A3.object_type = 'T' THEN A1.ticket_id END) AS q27, COUNT(DISTINCT CASE WHEN B3.address IS NOT NULL AND A1.dept_id = 4 AND B3.address IS NOT NULL AND A1.dept_id = 4 AND NOT A1.topic_id = 0 AND A3.object_type = 'T' THEN A1.ticket_id END) AS q28, COUNT(DISTINCT CASE WHEN B3.address IS NOT NULL AND A1.dept_id = 4 AND NOT A1.topic_id = 0 AND B3.address IS NOT NULL AND A1.dept_id = 4 AND NOT A1.topic_id = 0 AND A1.topic_id = 14 AND A3.object_type = 'T' THEN A1.ticket_id END) AS q29, COUNT(DISTINCT CASE WHEN B3.address IS NOT NULL AND A1.dept_id = 4 AND NOT A1.topic_id = 0 AND B3.address IS NOT NULL AND A1.dept_id = 4 AND NOT A1.topic_id = 0 AND A1.topic_id = 19 AND A3.object_type = 'T' THEN A1.ticket_id END) AS q30, COUNT(DISTINCT CASE WHEN B3.address IS NOT NULL AND A1.dept_id = 4 AND NOT A1.topic_id = 0 AND B3.address IS NOT NULL AND A1.dept_id = 4 AND NOT A1.topic_id = 0 AND A1.topic_id = 15 AND A3.object_type = 'T' THEN A1.ticket_id END) AS q31, COUNT(DISTINCT CASE WHEN B3.address IS NOT NULL AND A1.dept_id = 4 AND NOT A1.topic_id = 0 AND B3.address IS NOT NULL AND A1.dept_id = 4 AND NOT A1.topic_id = 0 AND A1.topic_id = 20 AND A3.object_type = 'T' THEN A1.ticket_id END) AS q32, COUNT(DISTINCT CASE WHEN B3.address IS NOT NULL AND A1.dept_id = 4 AND NOT A1.topic_id = 0 AND B3.address IS NOT NULL AND A1.dept_id = 4 AND NOT A1.topic_id = 0 AND A1.topic_id = 16 AND A3.object_type = 'T' THEN A1.ticket_id END) AS q33, COUNT(DISTINCT CASE WHEN B3.address IS NOT NULL AND A1.dept_id = 4 AND NOT A1.topic_id = 0 AND B3.address IS NOT NULL AND A1.dept_id = 4 AND NOT A1.topic_id = 0 AND A1.topic_id = 22 AND A3.object_type = 'T' THEN A1.ticket_id END) AS q34, COUNT(DISTINCT CASE WHEN B3.address IS NOT NULL AND A1.dept_id = 4 AND B6.value IS NOT NULL AND B3.address IS NOT NULL AND A1.dept_id = 4 AND B6.value IS NOT NULL AND B6.value REGEXP '\"?19(\"|,|$)' AND A3.object_type = 'T' THEN A1.ticket_id END) AS q35, COUNT(DISTINCT CASE WHEN B3.address IS NOT NULL AND A1.dept_id = 4 AND B6.value IS NOT NULL AND B3.address IS NOT NULL AND A1.dept_id = 4 AND B6.value IS NOT NULL AND B6.value REGEXP '\"?18(\"|,|$)' AND A3.object_type = 'T' THEN A1.ticket_id END) AS q36, COUNT(DISTINCT CASE WHEN B3.address IS NOT NULL AND A1.dept_id = 4 AND B3.address IS NOT NULL AND A1.dept_id = 4 AND B6.value IS NOT NULL AND A3.object_type = 'T' THEN A1.ticket_id END) AS q37, COUNT(DISTINCT CASE WHEN B3.address IS NOT NULL AND A1.dept_id = 4 AND B6.value IS NOT NULL AND B3.address IS NOT NULL AND A1.dept_id = 4 AND B6.value IS NOT NULL AND B6.value REGEXP '\"?17(\"|,|$)' AND A3.object_type = 'T' THEN A1.ticket_id END) AS q38, COUNT(DISTINCT CASE WHEN B3.address IS NOT NULL AND A1.dept_id = 4 AND B6.value IS NOT NULL AND B3.address IS NOT NULL AND A1.dept_id = 4 AND B6.value IS NOT NULL AND B6.value REGEXP '\"?11(\"|,|$)|\"?12(\"|,|$)' AND A3.object_type = 'T' THEN A1.ticket_id END) AS q39, COUNT(DISTINCT CASE WHEN B3.address IS NOT NULL AND A1.dept_id = 4 AND B6.value IS NOT NULL AND B3.address IS NOT NULL AND A1.dept_id = 4 AND B6.value IS NOT NULL AND B6.value REGEXP '\"?9(\"|,|$)' AND A3.object_type = 'T' THEN A1.ticket_id END) AS q40, COUNT(DISTINCT CASE WHEN B3.address IS NOT NULL AND B3.address IS NOT NULL AND A1.topic_id = 23 AND A3.object_type = 'T' THEN A1.ticket_id END) AS q41, COUNT(DISTINCT CASE WHEN B3.address IS NOT NULL AND A1.dept_id = 4 AND NOT A1.topic_id = 0 AND B3.address IS NOT NULL AND A1.dept_id = 4 AND NOT A1.topic_id = 0 AND A1.topic_id = 18 AND A3.object_type = 'T' THEN A1.ticket_id END) AS q42, COUNT(DISTINCT CASE WHEN B3.address IS NOT NULL AND A1.dept_id = 4 AND NOT A1.topic_id = 0 AND B3.address IS NOT NULL AND A1.dept_id = 4 AND NOT A1.topic_id = 0 AND A1.topic_id = 17 AND A3.object_type = 'T' THEN A1.ticket_id END) AS q43, COUNT(DISTINCT CASE WHEN B3.address IS NOT NULL AND A1.dept_id = 4 AND B6.value IS NOT NULL AND B3.address IS NOT NULL AND A1.dept_id = 4 AND B6.value IS NOT NULL AND B6.value REGEXP '\"?10(\"|,|$)' AND A3.object_type = 'T' THEN A1.ticket_id END) AS q44, COUNT(DISTINCT CASE WHEN B3.address IS NOT NULL AND A1.dept_id = 4 AND B6.value IS NOT NULL AND B3.address IS NOT NULL AND A1.dept_id = 4 AND B6.value IS NOT NULL AND B6.value REGEXP '\"?13(\"|,|$)' AND A3.object_type = 'T' THEN A1.ticket_id END) AS q45, COUNT(DISTINCT CASE WHEN B3.address IS NOT NULL AND A1.dept_id = 4 AND B6.value IS NOT NULL AND B3.address IS NOT NULL AND A1.dept_id = 4 AND B6.value IS NOT NULL AND B6.value REGEXP '\"?14(\"|,|$)' AND A3.object_type = 'T' THEN A1.ticket_id END) AS q46, COUNT(DISTINCT CASE WHEN B3.address IS NOT NULL AND A1.dept_id = 4 AND B6.value IS NOT NULL AND B3.address IS NOT NULL AND A1.dept_id = 4 AND B6.value IS NOT NULL AND B6.value REGEXP '\"?15(\"|,|$)' AND A3.object_type = 'T' THEN A1.ticket_id END) AS q47, COUNT(DISTINCT CASE WHEN B3.address IS NOT NULL AND A1.dept_id = 4 AND B6.value IS NOT NULL AND B3.address IS NOT NULL AND A1.dept_id = 4 AND B6.value IS NOT NULL AND B6.value REGEXP '\"?16(\"|,|$)' AND A3.object_type = 'T' THEN A1.ticket_id END) AS q48, COUNT(DISTINCT CASE WHEN B3.address IS NOT NULL AND A1.dept_id = 4 AND B6.value IS NOT NULL AND B3.address IS NOT NULL AND A1.dept_id = 4 AND B6.value IS NOT NULL AND B7.value = '1' AND A3.object_type = 'T' THEN A1.ticket_id END) AS q49, COUNT(DISTINCT CASE WHEN B3.address IS NOT NULL AND A1.dept_id = 4 AND B3.address IS NOT NULL AND A1.dept_id = 4 AND B8.value IS NOT NULL AND A3.object_type = 'T' THEN A1.ticket_id END) AS q50, COUNT(DISTINCT CASE WHEN B3.address IS NOT NULL AND A1.dept_id = 4 AND B8.value IS NOT NULL AND B3.address IS NOT NULL AND A1.dept_id = 4 AND B8.value IS NOT NULL AND B8.value REGEXP '\"?92(\"|,|$)' AND A3.object_type = 'T' THEN A1.ticket_id END) AS q51, COUNT(DISTINCT CASE WHEN B3.address IS NOT NULL AND A1.dept_id = 4 AND B8.value IS NOT NULL AND B3.address IS NOT NULL AND A1.dept_id = 4 AND B8.value IS NOT NULL AND B8.value REGEXP '\"?93(\"|,|$)' AND A3.object_type = 'T' THEN A1.ticket_id END) AS q52, COUNT(DISTINCT CASE WHEN B3.address IS NOT NULL AND A1.dept_id = 4 AND B8.value IS NOT NULL AND B3.address IS NOT NULL AND A1.dept_id = 4 AND B8.value IS NOT NULL AND B8.value REGEXP '\"?94(\"|,|$)' AND A3.object_type = 'T' THEN A1.ticket_id END) AS q53, COUNT(DISTINCT CASE WHEN B3.address IS NOT NULL AND A1.dept_id = 4 AND B8.value IS NOT NULL AND B3.address IS NOT NULL AND A1.dept_id = 4 AND B8.value IS NOT NULL AND B8.value REGEXP '\"?95(\"|,|$)' AND A3.object_type = 'T' THEN A1.ticket_id END) AS q54, COUNT(DISTINCT CASE WHEN B3.address IS NOT NULL AND A1.dept_id = 4 AND B8.value IS NOT NULL AND B3.address IS NOT NULL AND A1.dept_id = 4 AND B8.value IS NOT NULL AND B8.value REGEXP '\"?96(\"|,|$)' AND A3.object_type = 'T' THEN A1.ticket_id END) AS q55, COUNT(DISTINCT CASE WHEN B3.address IS NOT NULL AND A1.dept_id = 4 AND NOT A1.topic_id = 0 AND A1.topic_id = 14 AND B3.address IS NOT NULL AND A1.dept_id = 4 AND NOT A1.topic_id = 0 AND A1.topic_id = 14 AND C0.value = '1' AND A3.object_type = 'T' THEN A1.ticket_id END) AS q56, COUNT(DISTINCT CASE WHEN B3.address IS NOT NULL AND A1.dept_id = 4 AND NOT A1.topic_id = 0 AND A1.topic_id = 14 AND B3.address IS NOT NULL AND A1.dept_id = 4 AND NOT A1.topic_id = 0 AND A1.topic_id = 14 AND C1.value = '1' AND A3.object_type = 'T' THEN A1.ticket_id END) AS q57, COUNT(DISTINCT CASE WHEN B3.address IS NOT NULL AND A1.dept_id = 4 AND B3.address IS NOT NULL AND A1.dept_id = 4 AND C2.value IS NOT NULL AND A3.object_type = 'T' THEN A1.ticket_id END) AS q58, COUNT(DISTINCT CASE WHEN B3.address IS NOT NULL AND A1.dept_id = 4 AND C2.value IS NOT NULL AND B3.address IS NOT NULL AND A1.dept_id = 4 AND C2.value IS NOT NULL AND C2.value REGEXP '\"?297(\"|,|$)' AND A3.object_type = 'T' THEN A1.ticket_id END) AS q59, COUNT(DISTINCT CASE WHEN B3.address IS NOT NULL AND A1.dept_id = 4 AND C2.value IS NOT NULL AND B3.address IS NOT NULL AND A1.dept_id = 4 AND C2.value IS NOT NULL AND C2.value REGEXP '\"?299(\"|,|$)' AND A3.object_type = 'T' THEN A1.ticket_id END) AS q60, COUNT(DISTINCT CASE WHEN B3.address IS NOT NULL AND A1.dept_id = 4 AND C2.value IS NOT NULL AND B3.address IS NOT NULL AND A1.dept_id = 4 AND C2.value IS NOT NULL AND C2.value REGEXP '\"?300(\"|,|$)' AND A3.object_type = 'T' THEN A1.ticket_id END) AS q61, COUNT(DISTINCT CASE WHEN B3.address IS NOT NULL AND A1.dept_id = 4 AND C2.value IS NOT NULL AND B3.address IS NOT NULL AND A1.dept_id = 4 AND C2.value IS NOT NULL AND C2.value REGEXP '\"?301(\"|,|$)' AND A3.object_type = 'T' THEN A1.ticket_id END) AS q62, COUNT(DISTINCT CASE WHEN B3.address IS NOT NULL AND A1.dept_id = 4 AND C2.value IS NOT NULL AND B3.address IS NOT NULL AND A1.dept_id = 4 AND C2.value IS NOT NULL AND C2.value REGEXP '\"?302(\"|,|$)' AND A3.object_type = 'T' THEN A1.ticket_id END) AS q63, COUNT(DISTINCT CASE WHEN B3.address IS NOT NULL AND A1.dept_id = 4 AND C2.value IS NOT NULL AND B3.address IS NOT NULL AND A1.dept_id = 4 AND C2.value IS NOT NULL AND C2.value REGEXP '\"?298(\"|,|$)' AND A3.object_type = 'T' THEN A1.ticket_id END) AS q64, COUNT(DISTINCT CASE WHEN A2.state = 'open' AND A3.object_type = 'T' THEN A1.ticket_id END) AS q1, COUNT(DISTINCT CASE WHEN A2.state = 'open' AND A2.state = 'open' AND A1.isanswered = '0' AND A3.object_type = 'T' THEN A1.ticket_id END) AS q2, COUNT(DISTINCT CASE WHEN A1.staff_id IN (1) AND A2.state = 'open' AND A1.staff_id IN (1) AND A2.state = 'open' AND A1.staff_id IN (1) AND A3.object_type = 'T' THEN A1.ticket_id END) AS q6, COUNT(DISTINCT CASE WHEN A2.state = 'closed' AND A2.state = 'closed' AND A1.closed BETWEEN '2021-09-27 22:00:00' AND '2021-09-28 21:59:59' AND A3.object_type = 'T' THEN A1.ticket_id END) AS q9, COUNT(DISTINCT CASE WHEN A2.state = 'open' AND A2.state = 'open' AND A1.isanswered = '1' AND A3.object_type = 'T' THEN A1.ticket_id END) AS q3, COUNT(DISTINCT CASE WHEN A1.staff_id IN (1) AND A2.state = 'open' AND A1.staff_id IN (1) AND A2.state = 'open' AND NOT A1.staff_id IN (1) AND A3.object_type = 'T' THEN A1.ticket_id END) AS q7, COUNT(DISTINCT CASE WHEN A2.state = 'closed' AND A2.state = 'closed' AND A1.closed BETWEEN '2021-09-26 22:00:00' AND '2021-09-27 21:59:59' AND A3.object_type = 'T' THEN A1.ticket_id END) AS q10, COUNT(DISTINCT CASE WHEN A2.state = 'open' AND A2.state = 'open' AND A1.isoverdue = '1' AND A3.object_type = 'T' THEN A1.ticket_id END) AS q4, COUNT(DISTINCT CASE WHEN A1.staff_id IN (1) AND A2.state = 'open' AND A3.object_type = 'T' THEN A1.ticket_id END) AS q5, COUNT(DISTINCT CASE WHEN A2.state = 'closed' AND A2.state = 'closed' AND A1.closed BETWEEN '2021-09-26 22:00:00' AND '2021-10-03 21:59:59' AND A3.object_type = 'T' THEN A1.ticket_id END) AS q11, COUNT(DISTINCT CASE WHEN A2.state = 'closed' AND A3.object_type = 'T' THEN A1.ticket_id END) AS q8, COUNT(DISTINCT CASE WHEN A2.state = 'closed' AND A2.state = 'closed' AND A1.closed BETWEEN '2021-08-31 22:00:00' AND '2021-09-30 21:59:59' AND A3.object_type = 'T' THEN A1.ticket_id END) AS q12, COUNT(DISTINCT CASE WHEN A2.state = 'closed' AND A2.state = 'closed' AND A1.closed BETWEEN '2021-06-30 22:00:00' AND '2021-09-30 21:59:59' AND A3.object_type = 'T' THEN A1.ticket_id END) AS q13, COUNT(DISTINCT CASE WHEN A2.state = 'closed' AND A2.state = 'closed' AND A1.closed BETWEEN '2020-12-31 23:00:00' AND '2021-12-31 22:59:59' AND A3.object_type = 'T' THEN A1.ticket_id END) AS q14 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_department B0 ON (A4.object_type = 'D' AND A4.object_id = B0.id) LEFT JOIN ost_department B1 ON (A7.object_type = 'D' AND A7.object_id = B1.id) LEFT JOIN ost_user B2 ON (A1.user_id = B2.id) LEFT JOIN ost_user_email B3 ON (B2.id = B3.user_id) JOIN ost_form_entry B4 ON (B4.object_type = 'T' AND A1.ticket_id = B4.object_id) LEFT JOIN ost_form_entry_values B5 ON (B4.id = B5.entry_id) LEFT JOIN ost_form_entry_values B6 ON (B4.id = B6.entry_id) LEFT JOIN ost_form_entry_values B7 ON (B4.id = B7.entry_id) LEFT JOIN ost_form_entry_values B8 ON (B4.id = B8.entry_id) LEFT JOIN ost_form_entry_values C0 ON (B4.id = C0.entry_id) LEFT JOIN ost_form_entry_values C1 ON (B4.id = C1.entry_id) LEFT JOIN ost_form_entry_values C2 ON (B4.id = C2.entry_id) WHERE ((A2.state = 'open' AND (A1.staff_id = 1 OR A5.staff_id = 1 OR A6.object_type = 'C' AND A8.staff_id = 1)) OR (A2.state IN ('open', 'closed') AND (A1.dept_id IN (1, 3, 4, 5) OR B0.id IN (1, 3, 4, 5))) OR A6.object_type = 'C' AND B1.id IN (1, 3, 4, 5))

my queues are not much (about 10) and the filter criteria are really simple, every one checks one field with one condition.

hugs for everyone

KevinTheJedi

Well, depending on how crazy the agents setup these queues, no amount of server config/resources are going to be able to mitigate the potential insane query that is generated by that all in one approach. I mean, not trying to be mean, but this is just bad design. It's giving the end user more than enough rope to hang themselves and the rest of the team with.

Look at the query @fnixt posted.

There's no defending that mess... the patch needs to be made official.

eteich

We know slower systems and non-optimized systems can have issues which is why we have an optimization on our todo list (to account for the lower end systems).

Cheers.

For example the statistics should count only on user/agent login once.
And till he login again he sees the same statistical data.
If user/agent wana to update statistical data he should to login again which should not be so difficult for user/agent to login few times per day (when new stats are needed)