First, I simplified your syntax quite a bit to make it more readable:
SELECT DISTINCT b.direction
FROM modwork_beleg b
LEFT JOIN modwork_isu_isu_pkunde p ON b.id = p.beleg_id
LEFT JOIN modwork_isu_isu_vknr v ON b.id = v.beleg_id
LEFT JOIN modwork_isu_isu_gpnr g ON b.id = g.beleg_id
WHERE b.state IN ('neu', 'inarbeit', 'wiedervorlage')
AND (p.pkunde = 90237758
OR v.vknr = 254400297729
OR g.gpnr = 1001030921);
Next, and this is the important step concerning performance:
SELECT b.direction
FROM modwork_beleg b
JOIN modwork_isu_isu_pkunde p ON b.id = p.beleg_id
WHERE b.state IN ('neu', 'inarbeit', 'wiedervorlage')
AND p.pkunde = 90237758
UNION
SELECT b.direction
FROM modwork_beleg b
JOIN modwork_isu_isu_vknr v ON b.id = v.beleg_id
WHERE b.state IN ('neu', 'inarbeit', 'wiedervorlage')
AND v.vknr = 254400297729
UNION
SELECT b.direction
FROM modwork_beleg b
JOIN modwork_isu_isu_gpnr g ON b.id = g.beleg_id
WHERE b.state IN ('neu', 'inarbeit', 'wiedervorlage')
AND g.gpnr = 1001030921;
The point is, that in your original query you form one big table out of all possible combinations. If the tables modwork_isu_isu_*
each have, say, 100 rows per beleg_id
, this results in a huge table of 100x100x100 = 1 million rows, holding all the columns of all four tables. Then you select a couple of them (your query plan shows 32 resulting rows). This is extremely inefficient.
The good news is, your query can easily be broken into three parts. I did not test, but I'd bet this is faster by several orders of magnitude.
I also changed the join type from LEFT [OUTER] JOIN
to [INNER] JOIN
, because with three separate queries, the condition on the right table makes each a JOIN
effectively anyway.
Finally, using UNION
instead of UNION ALL
when combining the results of the three queries removes duplicate values in b.direction
. So I can drop the redundant DISTINCT
for each of the queries. All simpler and much faster.
Best Answer
save the unavailable hours as a special type of appointment