From 1473ab8375d4fea142ec06facee786c598a8de68 Mon Sep 17 00:00:00 2001 From: =?utf8?q?Felix=20D=C3=B6rre?= Date: Fri, 23 Dec 2016 11:45:21 +0100 Subject: [PATCH] fix: greatly improve performance of often-executed ping-fetch-query Change-Id: Ic574b193f65f1fd362bf7451fe343e0caa788910 --- .../gigi/database/DatabaseConnection.java | 2 +- .../cacert/gigi/database/tableStructure.sql | 3 ++- .../cacert/gigi/database/upgrade/from_25.sql | 1 + src/org/cacert/gigi/ping/PingerDaemon.java | 18 +++++++++--------- 4 files changed, 13 insertions(+), 11 deletions(-) create mode 100644 src/org/cacert/gigi/database/upgrade/from_25.sql diff --git a/src/org/cacert/gigi/database/DatabaseConnection.java b/src/org/cacert/gigi/database/DatabaseConnection.java index fd77be1e..2bdf9f1c 100644 --- a/src/org/cacert/gigi/database/DatabaseConnection.java +++ b/src/org/cacert/gigi/database/DatabaseConnection.java @@ -122,7 +122,7 @@ public class DatabaseConnection { } - public static final int CURRENT_SCHEMA_VERSION = 25; + public static final int CURRENT_SCHEMA_VERSION = 26; public static final int CONNECTION_TIMEOUT = 24 * 60 * 60; diff --git a/src/org/cacert/gigi/database/tableStructure.sql b/src/org/cacert/gigi/database/tableStructure.sql index 8b1a77d6..304d85d9 100644 --- a/src/org/cacert/gigi/database/tableStructure.sql +++ b/src/org/cacert/gigi/database/tableStructure.sql @@ -105,6 +105,7 @@ CREATE TABLE "domainPinglog" ( "challenge" varchar(16), "result" varchar(255) ); +CREATE INDEX ON "domainPinglog" ("configId","when"); DROP TABLE IF EXISTS "baddomains"; CREATE TABLE "baddomains" ( @@ -374,7 +375,7 @@ CREATE TABLE "schemeVersion" ( "version" smallint NOT NULL, PRIMARY KEY ("version") ); -INSERT INTO "schemeVersion" (version) VALUES(25); +INSERT INTO "schemeVersion" (version) VALUES(26); DROP TABLE IF EXISTS `passwordResetTickets`; CREATE TABLE `passwordResetTickets` ( diff --git a/src/org/cacert/gigi/database/upgrade/from_25.sql b/src/org/cacert/gigi/database/upgrade/from_25.sql new file mode 100644 index 00000000..59e92181 --- /dev/null +++ b/src/org/cacert/gigi/database/upgrade/from_25.sql @@ -0,0 +1 @@ +CREATE INDEX ON "domainPinglog" ("configId","when"); diff --git a/src/org/cacert/gigi/ping/PingerDaemon.java b/src/org/cacert/gigi/ping/PingerDaemon.java index a8d90f9c..0e4e3bd3 100644 --- a/src/org/cacert/gigi/ping/PingerDaemon.java +++ b/src/org/cacert/gigi/ping/PingerDaemon.java @@ -38,15 +38,15 @@ public class PingerDaemon extends Thread { } public void runWithConnection() { - searchNeededPings = new GigiPreparedStatement("SELECT `pc`.`id`" // - + " FROM `pingconfig` AS `pc`" // - + " INNER JOIN `domains` AS `d` ON `pc`.`domainid` = `d`.`id`" // - + " LEFT JOIN `domainPinglog` AS `dpl` ON `pc`.`id` = `dpl`.`configId`" // - + " WHERE `pc`.`deleted` IS NULL" // - + " AND `d`.`deleted` IS NULL" // - + " GROUP BY `pc`.`id`"// - + " HAVING ( MAX(`dpl`.`when`) < CURRENT_TIMESTAMP - interval '6 mons')" // - + " OR COUNT(`dpl`.`configId`) < 1"); + searchNeededPings = new GigiPreparedStatement("SELECT `pc`.`id`" // + + " FROM `pingconfig` AS `pc`" // + + " INNER JOIN `domains` AS `d` ON `d`.`id` = `pc`.`domainid`" // + + " WHERE `d`.`deleted` IS NULL" // + + " AND `pc`.`deleted` IS NULL" // + + " AND NOT EXISTS (" // + + " SELECT 1 FROM `domainPinglog` AS `dpl`" // + + " WHERE `dpl`.`configId` = `pc`.`id`" // + + " AND `dpl`.`when` >= CURRENT_TIMESTAMP - interval '6 mons')"); pingers.put(DomainPingType.EMAIL, new EmailPinger()); pingers.put(DomainPingType.SSL, new SSLPinger(truststore)); pingers.put(DomainPingType.HTTP, new HTTPFetch()); -- 2.39.2