Index on sqlite database

[Romuald Brunet]

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

[0001-drop-index-SINDEX-if-exists.patch]

---

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@...
http://mail.gnome.org/mailman/listinfo/evolution-hackers

[Paul Smith-5]

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@...
http://mail.gnome.org/mailman/listinfo/evolution-hackers

[Matthew Barnes]

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


_______________________________________________
Evolution-hackers mailing list
Evolution-hackers@...
http://mail.gnome.org/mailman/listinfo/evolution-hackers

[Srinivasa Ragavan]

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@...
http://mail.gnome.org/mailman/listinfo/evolution-hackers