Experimental changes...sqlite-refactor

1 files changed, 24 insertions, 4 deletions

diff --git a/addressbook/libedata-book/e-book-sqlite.c b/addressbook/libedata-book/e-book-sqlite.c
index 56f33ccf6..980fda59b 100644
--- a/addressbook/libedata-book/e-book-sqlite.c
+++ b/addressbook/libedata-book/e-book-sqlite.c
@@ -2384,10 +2384,16 @@ ebsql_init_aux_tables (EBookSqlite *ebsql,
 
 
 		if (success) {
-			/* Create an index on the 'uid' column, this speeds up inserts on large
-			 * addressbooks, because rows need to be deleted by UID before reinserting
+
+			/* Create an index on the implied 'uid' column, this is important
+			 * when replacing (modifying) contacts, since we need to remove
+			 * all rows in an auxiliary table which matches a given UID.
+			 *
+			 * This index speeds up the constraint in a statement such as:
+			 *
+			 *   DELETE from email_list WHERE email_list.uid = 'contact uid'
 			 */
-			tmp = g_strconcat ("UIDINDEX",
+			tmp = g_strconcat ("UID_INDEX",
 					   "_", field->dbname,
 					   "_", ebsql->priv->folderid,
 					   NULL);
@@ -4974,7 +4980,21 @@ ebsql_generate_select (EBookSqlite *ebsql,
 			if ((context->aux_mask & (1 << i)) != 0) {
 				SummaryField *field = &(ebsql->priv->summary_fields[i]);
 
-				ebsql_string_append_printf (string, " JOIN %Q AS %s ON %s.uid = summary.uid",
+				/* Note the '+' in the JOIN statement.
+				 *
+				 * This plus makes the uid's index ineligable to participate
+				 * in any indexing.
+				 *
+				 * Without this, the indexes which we prefer for prefix or
+				 * suffix matching in the auxiliary tables are ignored and
+				 * only considered on exact matches.
+				 *
+				 * This is crucial to ensure that the uid index does not
+				 * compete with the value index in constraints such as:
+				 *
+				 *     WHERE email_list.value LIKE "boogieman%"
+				 */
+				ebsql_string_append_printf (string, " JOIN %Q AS %s ON +%s.uid = summary.uid",
 							    field->aux_table,
 							    field->aux_table_symbolic,
 							    field->aux_table_symbolic);