Query optimization #1149
Replies: 3 comments
-
Dont look at this. It returns wrong results :-) |
Beta Was this translation helpful? Give feedback.
-
Instead, you can look at this. It doesn't bring huge improvement, but since we run this to get logs for given host and service, we can allow to change from LEFT JOIN to JOIN. SELECT |
Beta Was this translation helpful? Give feedback.
-
how much does it change things? mysql should be smart enough to see that there is a host_id filter and then use the corespoding index for that. |
Beta Was this translation helpful? Give feedback.
-
Hello.
On my environment the query, which Thruk runs on:
thruk/cgi-bin/showlog.cgi?host=XXXXX&service=XXXX
(I select the host, then given service on this host and I click View Alert History For This Service (Logs)
(the same for option View Notifications For This Service)
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
lLEFT JOIN
328c5_host
h ON l.host_id = h.host_idLEFT JOIN
328c5_service
s ON l.service_id = s.service_idLEFT JOIN
328c5_contact
c ON l.contact_id = c.contact_idWHERE (time >= 1638399600 AND time <= 1638486000 AND (l.host_id = 46))
ORDER BY l.time DESC
LIMIT 1000000
can even run for 80 seconds
Please take a look at given modification. On my environment it finishes in 15 seconds.
select
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,
"328c5" as peer_key
FROM
328c5_log
lleft join
328c5_host
h ON (l.host_id = h.host_id and h.host_id = 46)left JOIN
328c5_service
s ON h.host_id = s.host_idLEFT JOIN
328c5_contact
c ON l.contact_id = c.contact_idWHERE
(time >= 1638399600 AND time <= 1638486000)
ORDER BY l.time DESC
LIMIT 1000000
Beta Was this translation helpful? Give feedback.
All reactions