Bug 699597 - EBookSqlite: Use UNION for autocomplete queries

The sqlite query planner does a really bad job here. It ought to realise
that it could do the searches on fields in folder_id directly from the
folder_id table, indices and all.

So recogise this case for ourselves and rephrase it so the query planner
doesn't do so badly. If it's just a logical 'OR' of a bunch of conditions,
some of which are auxiliary fields and some are not, then use a basically
hand-crafted query using UNION to make sure sqlite notices the fast way
to do it.

This takes the autocomplete query on my 237000-entry EWS GAL from about
1700ms to 6ms.

Yes, it's an icky special case and it *really* ought to be considered
a sqlite bug. But it's an important special case because the user is
waiting for it while they type and delays are really noticeable. And
it's a *big* win.

Discussed at
https://www.mail-archive.com/sqlite-users@sqlite.org/msg86350.html
https://www.mail-archive.com/sqlite-users@sqlite.org/msg86643.html

1 files changed, 146 insertions, 16 deletions

diff --git a/addressbook/libedata-book/e-book-sqlite.c b/addressbook/libedata-book/e-book-sqlite.c
index 05e48532a..68689dfa6 100644
--- a/addressbook/libedata-book/e-book-sqlite.c
+++ b/addressbook/libedata-book/e-book-sqlite.c
@@ -899,21 +899,26 @@ search_data_from_results (gint ncol,
 {
 	EbSqlSearchData *data = g_slice_new0 (EbSqlSearchData);
 	gint i;
+	const gchar *name;
 
 	for (i = 0; i < ncol; i++) {
 
 		if (!names[i] || !cols[i])
 			continue;
 
+		name = names[i];
+		if (!strncmp (name, "summary.", 8))
+			name += 8;
+
 		/* These come through differently depending on the configuration,
 		 * search within text is good enough
 		 */
-		if (!g_ascii_strcasecmp (names[i], "uid")) {
+		if (!g_ascii_strcasecmp (name, "uid")) {
 			data->uid = g_strdup (cols[i]);
-		} else if (!g_ascii_strcasecmp (names[i], "vcard") ||
-			   !g_ascii_strncasecmp (names[i], "fetch_vcard", 11)) {
+		} else if (!g_ascii_strcasecmp (name, "vcard") ||
+			   !g_ascii_strncasecmp (name, "fetch_vcard", 11)) {
 			data->vcard = g_strdup (cols[i]);
-		} else if (!g_ascii_strcasecmp (names[i], "bdata")) {
+		} else if (!g_ascii_strcasecmp (name, "bdata")) {
 			data->extra = g_strdup (cols[i]);
 		}
 	}
@@ -5501,6 +5506,124 @@ ebsql_generate_select (EBookSqlite *ebsql,
 }
 
 static gboolean
+ebsql_is_autocomplete_query (PreflightContext *context)
+{
+	QueryFieldTest *test;
+	QueryElement **elements;
+	gint n_elements, i;
+	int non_aux_fields = 0;
+
+	if (context->status != PREFLIGHT_OK || context->aux_mask == 0)
+		return FALSE;
+
+	elements = (QueryElement **) context->constraints->pdata;
+	n_elements = context->constraints->len;
+
+	for (i = 0; i < n_elements; i++) {
+		test = (QueryFieldTest *) elements[i];
+
+		/* For these, check if the field being operated on is
+		   an auxiliary field or not. */
+		if (elements[i]->query == E_BOOK_QUERY_BEGINS_WITH ||
+		    elements[i]->query == E_BOOK_QUERY_BEGINS_WITH ||
+		    elements[i]->query == E_BOOK_QUERY_IS ||
+		    elements[i]->query == BOOK_QUERY_EXISTS ||
+		    elements[i]->query == E_BOOK_QUERY_CONTAINS) {
+			if (test->field->type != E_TYPE_CONTACT_ATTR_LIST)
+				non_aux_fields++;
+			continue;
+		}
+
+		/* Nothing else is allowed other than "(or" ... ")" */
+		if (elements[i]->query != BOOK_QUERY_SUB_OR &&
+		    elements[i]->query != BOOK_QUERY_SUB_END)
+			return FALSE;
+	}
+
+	/* If there were no non-aux fields being queried, don't bother */
+	return non_aux_fields != 0;
+}
+
+static EbSqlRowFunc
+ebsql_generate_autocomplete_query (EBookSqlite *ebsql,
+				   GString *string,
+				   SearchType search_type,
+				   PreflightContext *context,
+				   GError **error)
+{
+	QueryElement **elements;
+	gint n_elements, i;
+	guint64 aux_mask = context->aux_mask;
+	guint64 left_join_mask = context->left_join_mask;
+	EbSqlRowFunc callback;
+	gboolean first = TRUE;
+
+	elements = (QueryElement **) context->constraints->pdata;
+	n_elements = context->constraints->len;
+
+	/* First the queries which use aux tables. */
+	for (i = 0; i < n_elements; i++) {
+		GenerateFieldTest generate_test_func = NULL;
+		QueryFieldTest *test;
+		gint aux_index;
+
+		if (elements[i]->query == BOOK_QUERY_SUB_OR ||
+		    elements[i]->query == BOOK_QUERY_SUB_END)
+			continue;
+
+		test = (QueryFieldTest *) elements[i];
+		if (test->field->type != E_TYPE_CONTACT_ATTR_LIST)
+			continue;
+
+		aux_index = summary_field_get_index (ebsql, test->field_id);
+		context->aux_mask = (1 << aux_index);
+		context->left_join_mask = 0;
+
+		callback = ebsql_generate_select (ebsql, string, search_type, context, error);
+		g_string_append (string, " WHERE ");
+		context->aux_mask = aux_mask;
+		context->left_join_mask = left_join_mask;
+		if (!callback)
+			return NULL;
+
+		generate_test_func = field_test_func_table[test->query];
+		generate_test_func (ebsql, string, test);
+
+		g_string_append (string, " UNION ");
+	}
+	/* Finally, generate the SELECT for the primary fields. */
+	context->aux_mask = 0;
+	callback = ebsql_generate_select (ebsql, string, search_type, context, error);
+	context->aux_mask = aux_mask;
+	if (!callback)
+		return NULL;
+
+	g_string_append (string, " WHERE ");
+
+	for (i = 0; i < n_elements; i++) {
+		GenerateFieldTest generate_test_func = NULL;
+		QueryFieldTest *test;
+
+		if (elements[i]->query == BOOK_QUERY_SUB_OR ||
+		    elements[i]->query == BOOK_QUERY_SUB_END)
+			continue;
+
+		test = (QueryFieldTest *) elements[i];
+		if (test->field->type == E_TYPE_CONTACT_ATTR_LIST)
+			continue;
+
+		if (!first)
+			g_string_append (string, " OR ");
+		else
+			first = FALSE;
+
+		generate_test_func = field_test_func_table[test->query];
+		generate_test_func (ebsql, string, test);
+	}
+
+	return callback;
+}
+static gboolean
 ebsql_do_search_query (EBookSqlite *ebsql,
                        PreflightContext *context,
                        const gchar *sexp,
@@ -5517,18 +5640,25 @@ ebsql_do_search_query (EBookSqlite *ebsql,
 	 * during the preflight checks */
 	string = g_string_sized_new (GENERATED_QUERY_BYTES);
 
-	/* Generate the leading SELECT statement */
-	callback = ebsql_generate_select (
-		ebsql, string, search_type, context, error);
-
-	if (callback &&
-	    EBSQL_STATUS_GEN_CONSTRAINTS (context->status)) {
-		/*
-		 * Now generate the search expression on the main contacts table
-		 */
-		g_string_append (string, " WHERE ");
-		ebsql_generate_constraints (
-			ebsql, string, context->constraints, sexp);
+	/* Extra special case. For the common case of the email composer's
+	   addressbook autocompletion, we really want the most optimal query.
+	   So check for it and use a basically hand-crafted one. */
+        if (ebsql_is_autocomplete_query(context)) {
+		callback = ebsql_generate_autocomplete_query (ebsql, string, search_type, context, error);
+	} else {
+		/* Generate the leading SELECT statement */
+		callback = ebsql_generate_select (
+						  ebsql, string, search_type, context, error);
+
+		if (callback &&
+		    EBSQL_STATUS_GEN_CONSTRAINTS (context->status)) {
+			/*
+			 * Now generate the search expression on the main contacts table
+			 */
+			g_string_append (string, " WHERE ");
+			ebsql_generate_constraints (
+				ebsql, string, context->constraints, sexp);
+		}
 	}
 
 	if (callback)