Query optimization 2

[tomaszdubiel18]

Hello.
Just like in:
#1149
I select the host, then given service on this host and I click View Alert History For This Service (Outages)
thruk/cgi-bin/extinfo.cgi?type=2&host=XXXX&service=XXXXX
Thruk executes this query:

SELECT
l.time as time,
l.class as class,
l.type as type,
l.state as state,
l.state_type as state_type,
IFNULL(h.host_name, "") as host_name,
IFNULL(s.service_description, "") as service_description,
IFNULL(c.name, "") as contact_name,
l.message as message,
"328c5" as peer_key
FROM
328c5_log l
LEFT JOIN 328c5_host h ON l.host_id = h.host_id
LEFT JOIN 328c5_service s ON l.service_id = s.service_id
LEFT JOIN 328c5_contact c ON l.contact_id = c.contact_id
WHERE ((time >= 1638054000 AND time <= 1638486000)
AND ((l.host_id = 1 OR l.host_id = 2 OR l.host_id = 3 OR l.host_id = 4 OR l.host_id = 5 OR l.host_id = 168 OR l.host_id = 6 OR l.host_id = 7 OR l.host_id = 8 OR l.host_id = 9 OR l.host_id = 10 OR l.host_id = 11 OR l.host_id = 12 OR l.host_id = 13 OR l.host_id = 14 OR l.host_id = 15 OR l.host_id = 16 OR l.host_id = 17 OR l.host_id = 18 OR l.host_id = 19 OR l.host_id = 20 OR l.host_id = 169 OR l.host_id = 21 OR l.host_id = 22 OR l.host_id = 23 OR l.host_id = 24 OR l.host_id = 25 OR l.host_id = 26 OR l.host_id = 27 OR l.host_id = 28 OR l.host_id = 29 OR l.host_id = 30 OR l.host_id = 31 OR l.host_id = 32 OR l.host_id = 33 OR l.host_id = 34 OR l.host_id = 35 OR l.host_id = 36 OR l.host_id = 37 OR l.host_id = 38 OR l.host_id = 39 OR l.host_id = 40 OR l.host_id = 41 OR l.host_id = 42 OR l.host_id = 170 OR l.host_id = 43 OR l.host_id = 44 OR l.host_id = 45 OR l.host_id = 46 OR l.host_id = 47 OR l.host_id = 48 OR l.host_id = 49 OR l.host_id = 50 OR l.host_id = 51 OR l.host_id = 52 OR l.host_id = 53 OR l.host_id = 54 OR l.host_id = 55 OR l.host_id = 56 OR l.host_id = 57 OR l.host_id = 58 OR l.host_id = 59 OR l.host_id = 60 OR l.host_id = 61 OR l.host_id = 62 OR l.host_id = 63 OR l.host_id = 64 OR l.host_id = 65 OR l.host_id = 66 OR l.host_id = 67 OR l.host_id = 68 OR l.host_id = 69 OR l.host_id = 70 OR l.host_id = 71 OR l.host_id = 72 OR l.host_id = 73 OR l.host_id = 74 OR l.host_id = 75 OR l.host_id = 76 OR l.host_id = 77 OR l.host_id = 78 OR l.host_id = 79 OR l.host_id = 80 OR l.host_id = 81 OR l.host_id = 82 OR l.host_id = 83 OR l.host_id = 84 OR l.host_id = 85 OR l.host_id = 86 OR l.host_id = 87 OR l.host_id = 88 OR l.host_id = 89 OR l.host_id = 90 OR l.host_id = 91 OR l.host_id = 92 OR l.host_id = 93 OR l.host_id = 94 OR l.host_id = 95 OR l.host_id = 172 OR l.host_id = 96 OR l.host_id = 97 OR l.host_id = 98 OR l.host_id = 171 OR l.host_id = 99 OR l.host_id = 100 OR l.host_id = 101 OR l.host_id = 102 OR l.host_id = 103 OR l.host_id = 104 OR l.host_id = 105 OR l.host_id = 106 OR l.host_id = 107 OR l.host_id = 108 OR l.host_id = 109 OR l.host_id = 110 OR l.host_id = 111 OR l.host_id = 112 OR l.host_id = 113 OR l.host_id = 114 OR l.host_id = 115 OR l.host_id = 116 OR l.host_id = 117 OR l.host_id = 118 OR l.host_id = 119 OR l.host_id = 120 OR l.host_id = 121 OR l.host_id = 122 OR l.host_id = 123 OR l.host_id = 124 OR l.host_id = 125 OR l.host_id = 126 OR l.host_id = 127 OR l.host_id = 128 OR l.host_id = 129 OR l.host_id = 130 OR l.host_id = 131 OR l.host_id = 132 OR l.host_id = 133 OR l.host_id = 134 OR l.host_id = 135 OR l.host_id = 136 OR l.host_id = 137 OR l.host_id = 138 OR l.host_id = 139 OR l.host_id = 140 OR l.host_id = 141 OR l.host_id = 142 OR l.host_id = 143 OR l.host_id = 144 OR l.host_id = 145 OR l.host_id = 146 OR l.host_id = 147 OR l.host_id = 148 OR l.host_id = 149 OR l.host_id = 150 OR l.host_id = 151 OR l.host_id = 152 OR l.host_id = 153 OR l.host_id = 154 OR l.host_id = 155 OR l.host_id = 156 OR l.host_id = 157 OR l.host_id = 158 OR l.host_id = 159 OR l.host_id = 160 OR l.host_id = 161 OR l.host_id = 162 OR l.host_id = 163 OR l.host_id = 164 OR l.host_id = 165 OR l.host_id = 166) AND ((type = 'HOST ALERT' AND (state_type = 'HARD' OR state = 0)) OR (type = 'INITIAL HOST STATE' AND (state_type = 'HARD' OR state = 0)) OR (type = 'CURRENT HOST STATE' AND (state_type = 'HARD' OR state = 0)) OR type = 'HOST DOWNTIME ALERT')))

First of all, I would optimize this query similarly just like in the mentioned topic, but secondly: if I run this for given host and for given service, why is there even this part: host_id = or host_id = or .......?
If Thruk wants to run this for ALL HOSTS, why is there even this part? :-)
However, I run this for GIVEN HOST and GIVEN SERVICE, so in the query there should be only one host id.

[sni]

That sounds wrong, for a given host/service there should only be a single WHERE statement.

[tomaszdubiel18]

Should I create issue from this discussion?

[sni]

i just tried and cannot reproduce this. What's the final url where you get this query? And which roles does this user have (from cgi.cfg)

[tomaszdubiel18]

thruk/cgi-bin/avail.cgi?outages=1&host=XXXX&service=XXXX
I check on thrukadmin

[tomaszdubiel18]

Problem persists in latest version.
Thats how I described, Thruk runs this query:
SELECT
l.time as time,
l.class as class,
l.type as type,
l.state as state,
l.state_type as state_type,
IFNULL(h.host_name, "") as host_name,
IFNULL(s.service_description, "") as service_description,
IFNULL(c.name, "") as contact_name,
l.message as message,
"328c5" as peer_key
FROM
328c5_log l
LEFT JOIN 328c5_host h ON l.host_id = h.host_id
LEFT JOIN 328c5_service s ON l.service_id = s.service_id
LEFT JOIN 328c5_contact c ON l.contact_id = c.contact_id
WHERE ((time >= 1639350000 AND time <= 1639782000) AND ((l.host_id = 1 OR l.host_id = 2 OR l.host_id = 3 OR l.host_id = 4 OR l.host_id = 5 OR l.host_id = 168 OR l.host_id = 6 OR l.host_id = 7 OR l.host_id = 8 OR l.host_id = 9 OR l.host_id = 10 OR l.host_id = 11 OR l.host_id = 12 OR l.host_id = 13 OR l.host_id = 14 OR l.host_id = 15 OR l.host_id = 16 OR l.host_id = 17 OR l.host_id = 18 OR l.host_id = 19 OR l.host_id = 20 OR l.host_id = 169 OR l.host_id = 21 OR l.host_id = 22 OR l.host_id = 23 OR l.host_id = 24 OR l.host_id = 25 OR l.host_id = 26 OR l.host_id = 27 OR l.host_id = 28 OR l.host_id = 29 OR l.host_id = 30 OR l.host_id = 31 OR l.host_id = 32 OR l.host_id = 33 OR l.host_id = 34 OR l.host_id = 35 OR l.host_id = 36 OR l.host_id = 37 OR l.host_id = 38 OR l.host_id = 39 OR l.host_id = 40 OR l.host_id = 41 OR l.host_id = 42 OR l.host_id = 170 OR l.host_id = 43 OR l.host_id = 44 OR l.host_id = 45 OR l.host_id = 46 OR l.host_id = 47 OR l.host_id = 48 OR l.host_id = 49 OR l.host_id = 50 OR l.host_id = 51 OR l.host_id = 52 OR l.host_id = 53 OR l.host_id = 54 OR l.host_id = 55 OR l.host_id = 56 OR l.host_id = 57 OR l.host_id = 58 OR l.host_id = 59 OR l.host_id = 60 OR l.host_id = 61 OR l.host_id = 62 OR l.host_id = 63 OR l.host_id = 64 OR l.host_id = 65 OR l.host_id = 66 OR l.host_id = 67 OR l.host_id = 68 OR l.host_id = 69 OR l.host_id = 70 OR l.host_id = 71 OR l.host_id = 72 OR l.host_id = 73 OR l.host_id = 74 OR l.host_id = 75 OR l.host_id = 76 OR l.host_id = 77 OR l.host_id = 78 OR l.host_id = 79 OR l.host_id = 80 OR l.host_id = 81 OR l.host_id = 82 OR l.host_id = 83 OR l.host_id = 84 OR l.host_id = 85 OR l.host_id = 86 OR l.host_id = 87 OR l.host_id = 88 OR l.host_id = 89 OR l.host_id = 90 OR l.host_id = 91 OR l.host_id = 92 OR l.host_id = 93 OR l.host_id = 94 OR l.host_id = 95 OR l.host_id = 174 OR l.host_id = 172 OR l.host_id = 96 OR l.host_id = 97 OR l.host_id = 98 OR l.host_id = 171 OR l.host_id = 173 OR l.host_id = 99 OR l.host_id = 100 OR l.host_id = 101 OR l.host_id = 102 OR l.host_id = 103 OR l.host_id = 104 OR l.host_id = 105 OR l.host_id = 106 OR l.host_id = 107 OR l.host_id = 108 OR l.host_id = 109 OR l.host_id = 110 OR l.host_id = 111 OR l.host_id = 112 OR l.host_id = 113 OR l.host_id = 114 OR l.host_id = 115 OR l.host_id = 116 OR l.host_id = 117 OR l.host_id = 118 OR l.host_id = 119 OR l.host_id = 120 OR l.host_id = 121 OR l.host_id = 122 OR l.host_id = 123 OR l.host_id = 124 OR l.host_id = 125 OR l.host_id = 126 OR l.host_id = 127 OR l.host_id = 128 OR l.host_id = 129 OR l.host_id = 130 OR l.host_id = 131 OR l.host_id = 132 OR l.host_id = 133 OR l.host_id = 134 OR l.host_id = 135 OR l.host_id = 136 OR l.host_id = 137 OR l.host_id = 138 OR l.host_id = 139 OR l.host_id = 140 OR l.host_id = 141 OR l.host_id = 142 OR l.host_id = 143 OR l.host_id = 144 OR l.host_id = 145 OR l.host_id = 146 OR l.host_id = 147 OR l.host_id = 148 OR l.host_id = 149 OR l.host_id = 150 OR l.host_id = 151 OR l.host_id = 152 OR l.host_id = 153 OR l.host_id = 154 OR l.host_id = 155 OR l.host_id = 156 OR l.host_id = 157 OR l.host_id = 158 OR l.host_id = 159 OR l.host_id = 160 OR l.host_id = 161 OR l.host_id = 162 OR l.host_id = 163 OR l.host_id = 164 OR l.host_id = 165 OR l.host_id = 166) AND ((type = 'HOST ALERT' AND (state_type = 'HARD' OR state = 0)) OR (type = 'INITIAL HOST STATE' AND (state_type = 'HARD' OR state = 0)) OR (type = 'CURRENT HOST STATE' AND (state_type = 'HARD' OR state = 0)) OR type = 'HOST DOWNTIME ALERT')))