Re: [Evolution-hackers] Index on sqlite database
On Sat, 2009-08-01 at 13:45 -0400, Matthew Barnes wrote: > On Sat, 2009-08-01 at 11:37 -0400, Paul Smith wrote: > > Have any of the Evo hackers looked at this email from Romuald? This > > seems like a simple change that should be made. > > > > Maybe a bugzilla entry is needed? > > I commented in the bug he filed, but I also want Srini's thoughts: > http://bugzilla.gnome.org/show_bug.cgi?id=590044 Just replied on the bug. -Srini ___ Evolution-hackers mailing list Evolution-hackers@gnome.org http://mail.gnome.org/mailman/listinfo/evolution-hackers
Re: [Evolution-hackers] Index on sqlite database
On Sat, 2009-08-01 at 11:37 -0400, Paul Smith wrote: > Have any of the Evo hackers looked at this email from Romuald? This > seems like a simple change that should be made. > > Maybe a bugzilla entry is needed? I commented in the bug he filed, but I also want Srini's thoughts: http://bugzilla.gnome.org/show_bug.cgi?id=590044 Matthew Barnes signature.asc Description: This is a digitally signed message part ___ Evolution-hackers mailing list Evolution-hackers@gnome.org http://mail.gnome.org/mailman/listinfo/evolution-hackers
Re: [Evolution-hackers] Index on sqlite database
On Tue, 2009-06-23 at 18:28 +0200, Romuald Brunet wrote: > 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). Have any of the Evo hackers looked at this email from Romuald? This seems like a simple change that should be made. Maybe a bugzilla entry is needed? ___ Evolution-hackers mailing list Evolution-hackers@gnome.org http://mail.gnome.org/mailman/listinfo/evolution-hackers
[Evolution-hackers] Index on sqlite database
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
___
Evolution-hackers mailing list
Evolution-hackers@gnome.org
http://mail.gnome.org/mailman/listinfo/evolution-hackers
