add_dest_addr_idx.patch

From 62901491d19f0ac203cec02ea3a150fe6b15cb85 Mon Sep 17 00:00:00 2001
From: Alexander Nikolaev <variomap@gmail.com>
Date: Fri, 27 Mar 2015 16:50:32 +0100
Subject: [PATCH] db: create index for dest_addr field in sms table

This field is used for joining SMS and Subscriber tables every time
unsent sms is retrived. Indexing that field speeds up query ~4 times on
test database with 8500 records in both SMS and Subscriber tables.
---
 openbsc/src/libmsc/db.c | 11 +++++++++++
 1 file changed, 11 insertions(+)

diff --git a/openbsc/src/libmsc/db.c b/openbsc/src/libmsc/db.c
index bdecbb4..f090c45 100644
--- a/openbsc/src/libmsc/db.c
+++ b/openbsc/src/libmsc/db.c
@@ -55,6 +55,7 @@ enum {
 	SCHEMA_EQUIPMENT,
 	SCHEMA_EQUIPMENT_WATCH,
 	SCHEMA_SMS,
+	SCHEMA_SMS_IDX,
 	SCHEMA_VLR,
 	SCHEMA_APDU,
 	SCHEMA_COUNTERS,
@@ -133,6 +134,8 @@ static const char *create_stmts[] = {
 		"header BLOB, "		/* UD Header */
 		"text TEXT "		/* decoded UD after UDH */
 		")",
+  /* used by db_sms_get_unsent() query */
+  [SCHEMA_SMS_IDX] = "CREATE INDEX IF NOT EXISTS idx_sms_dest_addr ON SMS(dest_addr)", 
 	[SCHEMA_VLR] = "CREATE TABLE IF NOT EXISTS VLR ("
 		"id INTEGER PRIMARY KEY AUTOINCREMENT, "
 		"created TIMESTAMP NOT NULL, "
@@ -300,6 +303,14 @@ static int update_db_revision_3(void)
 	}
 	dbi_result_free(result);
 
+	result = dbi_conn_query(conn, create_stmts[SCHEMA_SMS_IDX]);
+	if (!result) {
+		LOGP(DDB, LOGL_ERROR,
+		     "Failed to create an index for the SMS table (dest_addr).\n");
+		goto rollback;
+	}
+	dbi_result_free(result);
+
 	/* Cycle through old messages and convert them to the new format */
 	result = dbi_conn_queryf(conn, "SELECT * FROM SMS_3");
 	if (!result) {
-- 
1.9.5 (Apple Git-50.3)