Improve log pruning performance

benchmark                      old ns/op     new ns/op     delta
BenchmarkDeleteLogBefore-4     110507581     9547903       -91.36%

2 files changed, 21 insertions, 0 deletions

diff --git a/sql/sql.go b/sql/sql.go
index 5941a00..8ad0f9d 100644
--- a/sql/sql.go
+++ b/sql/sql.go
@@ -47,6 +47,9 @@ CREATE TABLE IF NOT EXISTS log (
 );
 
 CREATE INDEX IF NOT EXISTS log_time ON log(time);
+CREATE INDEX IF NOT EXISTS log_remote_addr_id ON log(remote_addr_id);
+CREATE INDEX IF NOT EXISTS log_rr_question_id ON log(rr_question_id);
+CREATE INDEX IF NOT EXISTS log_rr_type_id ON log(rr_type_id);
 
 CREATE TABLE IF NOT EXISTS log_rr_answer (
   id                INTEGER           PRIMARY KEY,
@@ -57,6 +60,7 @@ CREATE TABLE IF NOT EXISTS log_rr_answer (
 );
 
 CREATE INDEX IF NOT EXISTS log_rr_answer_log_id ON log_rr_answer(log_id);
+CREATE INDEX IF NOT EXISTS log_rr_answer_rr_answer_id ON log_rr_answer(rr_answer_id);
 
 CREATE TABLE IF NOT EXISTS cache (
   id                INTEGER           PRIMARY KEY,
diff --git a/sql/sql_test.go b/sql/sql_test.go
index 72a4df8..50746ed 100644
--- a/sql/sql_test.go
+++ b/sql/sql_test.go
@@ -231,3 +231,20 @@ func BenchmarkReadLog(b *testing.B) {
 		c.readLog(1000)
 	}
 }
+
+func BenchmarkDeleteLogBefore(b *testing.B) {
+	c := testClient()
+	for n := 0; n < b.N; n++ {
+		b.StopTimer()
+		// Generate test data with many unique values for each column
+		for i := 0; i < 16; i++ {
+			for j := 0; j < 256; j++ {
+				if err := c.writeLog(time.Now(), net.ParseIP(fmt.Sprintf("127.0.%d.%d", i, j)), false, 1, fmt.Sprintf("%d-%d.example.com.", i, j), fmt.Sprintf("127.1.%d.%d", i, j)); err != nil {
+					b.Fatal(err)
+				}
+			}
+		}
+		b.StartTimer()
+		c.deleteLogBefore(time.Now())
+	}
+}