[Evolution-hackers] Index on sqlite database

[Romuald Brunet <romuald brunet gmail com>]

Hi people

I've been using Evolution for quite some time now, with a good number
of emails (4 millions total)

After a bit of exploring the folders.db sqlite file, I've found out
that there is an index on every table named SINDEX-table that is an
index for *every* column in the table.

In practice, that means that the index will never be used, since it
would require a query mixing all those fields (or at least, the first
ones in order : uid, flags, size, .. )

It also means that the index will take unnecessary place on the
filesystem (in my case, that was around 100MB by the time I removed
them), and slow down inserts into the database (in my case, when
moving a lots of mails from a folder to another).

I've patched my evolution version and I'm working with it since at
least 6 months without any problem. So I thought I might as well
provide it to you :)

Regards
--
Romuald Brunet

diff --git a/camel/camel-db.c b/camel/camel-db.c
index 34007e6..6fb96ef 100644
--- a/camel/camel-db.c
+++ b/camel/camel-db.c
@@ -1164,7 +1164,7 @@ camel_db_create_message_info_table (CamelDB *cdb, const gchar *folder_name, Came
 
 	/* FIXME: sqlize folder_name before you create the index */
 	safe_index = g_strdup_printf("SINDEX-%s", folder_name);
-	table_creation_query = sqlite3_mprintf ("CREATE INDEX IF NOT EXISTS %Q ON %Q (uid, flags, size, dsent, dreceived, subject, mail_from, mail_to, mail_cc, mlist, part, labels, usertags, cinfo, bdata)", safe_index, folder_name);
+	table_creation_query = sqlite3_mprintf ("DROP INDEX IF EXISTS %Q", safe_index);
 	ret = camel_db_add_to_transaction (cdb, table_creation_query, ex);
 	g_free (safe_index);
 	sqlite3_free (table_creation_query);
-- 
1.6.0.4