Hi. Ich habe hier noch so eine Abfrage wo man sicher noch Performance rausholen kann. Im Unterschied zu der oberen Abfrage geht diese über mehrere Tabellen:
[code=SQL]
SELECT DISTINCT t.id,
t.tnumber,
c.company,
t.user29,
t.status,
IF (d.Zeit IS NULL, f.Zeit, d.Zeit) AS Zeit,
IFNULL(d.Aktionen, 0) + IFNULL(f.Anrufe, 0) AS Aktionen,
IFNULL(z.Aktionen, 0) + IFNULL(f.Anrufe, 0) AS AktGesamt
FROM crmdb.tickets t JOIN crmdb.contacts c ON t.cid = c.id
LEFT JOIN (SELECT COUNT(a.id) AS Aktionen,
a.ticket_id,
from_unixtime(a.chgtime, '%H:%i') AS Zeit
FROM crmdb.tickets t JOIN crmdb.ticket_actions a ON t.id = a.ticket_id
WHERE a.createuser =59
AND from_unixtime(a.action_date, '%Y-%m-%d') = '2019-10-01'
GROUP BY t.tnumber) d ON t.id = d.ticket_id
LEFT JOIN (SELECT COUNT(l.ttid) AS Anrufe,
l.ttid,
from_unixtime(l.chgtime, '%H:%i') AS Zeit
FROM crmdb.tickets t JOIN crmdb.calls l ON t.id = l.ttid
WHERE l.createuser =59
AND from_unixtime(l.createtime, '%Y-%m-%d') = '2019-10-01'
GROUP BY t.tnumber) f ON t.id = f.ttid
LEFT JOIN (SELECT COUNT(a.id) AS Aktionen,
a.ticket_id
FROM crmdb.tickets t JOIN crmdb.ticket_actions a ON t.id = a.ticket_id
GROUP BY t.tnumber) z ON t.id = z.ticket_id
WHERE t.id IN (SELECT DISTINCT a.ticket_id
FROM crmdb.ticket_actions a
WHERE a.ticket_id = t.id AND a.createuser =59
AND from_unixtime(a.action_date, '%Y-%m-%d') = '2019-10-01')
OR t.id IN (SELECT DISTINCT l.ttid
FROM crmdb.calls l
WHERE l.ttid = t.id
AND l.createuser =59
AND from_unixtime(l.createtime, '%Y-%m-%d') = '2019-10-01')
GROUP BY t.tnumber
[/code]
Die Abfrage funktioniert, nur eben langsam... kann mir jemand helfen ?
