Re: [Monotone-devel] Monotone speedup by adding additional database indices?

[Ralf S. Engelschall]

On Wed, Oct 10, 2007, Justin Patrin wrote:

> On 10/10/07, Ben Walton <address@hidden> wrote:
> > Indexes speed up read operations but slow down writes.  Which do we do
> > more of?  I'd optimize for the case that would benefit most.  I
> > suspect we do a fair amount of both.  In this case, maybe adding
> > indexes for mostly-read tables would be the way to go.
> >
>
> The size increase on the DB should also be investigated here. How much
> did your DB increase in size when the indexes were added?

With the database where all n.v.m.* revisions stay:

| $ sqlite mtn.db
| -- Loading resources from /u/rse/.sqliterc
| SQLite version 3.5.1
| Enter ".help" for instructions
| sqlite> VACUUM;
| $ ls -l
| total 141344
| -rw-r--r--  1 rse  rse  144629760 Oct 11 07:52 mtn.db
| $ sqlite mtn.db
| -- Loading resources from /u/rse/.sqliterc
| SQLite version 3.5.1
| Enter ".help" for instructions
| sqlite> CREATE INDEX revision_certs__id_name_value ON revision_certs (id, 
name, value);
| sqlite> CREATE INDEX public_keys__id ON public_keys (id);
| sqlite> VACUUM;
| $ ls -l
| total 147024
| -rw-r--r--  1 rse  rse  150446080 Oct 11 07:53 mtn.db
| $ bc
| bc 1.06
| Copyright 1991-1994, 1997, 1998, 2000 Free Software Foundation, Inc.
| This is free software with ABSOLUTELY NO WARRANTY.
| For details type `warranty'.
| scale = 2
| 150446080/144629760
| 1.04

So, the indices increased the database by just 4% and this IMHO is more
or less negligible...

                                       Ralf S. Engelschall
                                       address@hidden
                                       www.engelschall.com