Too Many Queries Running On Getting New Hosts #39
Description
Within the controllers.py file there's the get_qualifying_crackers method. We've noticed that after doing the initial query of the crackers table that it returns the id and ip_address in order to query the reports table. Looking at the function it looks as if we could reduce the added on queries with the queries below. It looks like all of the data needed is in the crackers table, so there's no need to query the reports table.
I think this could be used for lines 103 - 156. Let me know if there's anything I'm missing in the code that's not being handled in the SQL.
SELECT
DISTINCT ip_address
FROM
crackers
where
#check from last sync
latest_time > 1590029927
#check a and b (reports and resiliency)
(
current_reports >= 3
AND resiliency >= 3600
) OR
# check c and d
(
# this is a resiliency check
AND latest_time - first_time >= 3600
)
order by latest_time ASC;
This is a portion of the log, so you can see what's happening.
SELECT DISTINCT c.id, c.ip_address
FROM crackers c
WHERE (c.current_reports >= 3)
AND (c.resiliency >= 18000)
AND (c.latest_time >= 1590033382)
ORDER BY c.first_time DESC
28 Query COMMIT
12 Query SELECT * FROM reports WHERE cracker_id = 291909 ORDER BY first_report_time ASC
109 Query COMMIT
12 Query COMMIT
52 Query SELECT * FROM crackers WHERE id = 299601
43 Query SELECT * FROM reports WHERE cracker_id = 287737 ORDER BY first_report_time ASC
52 Query COMMIT
61 Query SELECT * FROM reports WHERE cracker_id = 251808 ORDER BY first_report_time ASC
123 Query SELECT * FROM crackers WHERE id = 311074
123 Query COMMIT
61 Query COMMIT
43 Query COMMIT
127 Query SELECT * FROM crackers WHERE id = 291040
127 Query COMMIT
113 Query SELECT * FROM crackers WHERE id = 289900
21 Query SELECT * FROM reports WHERE cracker_id = 300001 ORDER BY first_report_time ASC
113 Query COMMIT
140 Query SELECT * FROM reports WHERE cracker_id = 268511 ORDER BY first_report_time ASC
21 Query COMMIT
45 Query SELECT * FROM reports WHERE cracker_id = 299803 ORDER BY first_report_time ASC
140 Query COMMIT
45 Query COMMIT
23 Query SELECT * FROM crackers WHERE id = 261267
30 Query SELECT * FROM reports WHERE cracker_id = 296089 ORDER BY first_report_time ASC