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.
-Ben On 10/10/07, Ralf S. Engelschall <[EMAIL PROTECTED]> wrote: > Some Monotone operations really operate slower than what one would > expect in the first spot. Hence, I've today looked at the run-time of > a simple "mtn update" in a workspace which *is already* at h:n.v.m. > This "no-operation" command internally performs a dozend times the > following SQL queries: > > SELECT id, name, value, keypair, signature > FROM revision_certs WHERE id = ? AND name = ? AND value = ? > SELECT keydata FROM public_keys WHERE id = ? > SELECT id FROM public_keys WHERE id = ? > > The problem is that "revision_certs" and "public_keys" have not the > proper indices for those queries and hence full-table scans seem to > be performed. I did a quick test and added the following to indices > manually: > > CREATE INDEX revision_certs__id_name_value ON > revision_certs (id, name, value); > CREATE INDEX public_keys__id ON > public_keys (id); > > This dropped down the total execution time of the mentioned "mtn update" > command by over 80%! A "time mtn update" showed 0.450s on average before > and 0.080s on average afterwards. And this was really not any type of > in-depth analysis of the situation. I just created two obvious indices > for the most prominent queries which "mtn --debug update" showed me. > > What do we think? Should we investigate further and especially add > additional indices like the above to the Monotone database schema? Or is > there consensus that this type of speed optimization is just the root of > furthcoming evil and at least at this time should be still ignored at > all... > Ralf S. Engelschall > [EMAIL PROTECTED] > www.engelschall.com > > > > _______________________________________________ > Monotone-devel mailing list > [email protected] > http://lists.nongnu.org/mailman/listinfo/monotone-devel > -- --------------------------------------------------------------------------------------------------------------------------- Ben Walton <[EMAIL PROTECTED]> When one person suffers from a delusion, it is called insanity. When many people suffer from a delusion it is called Religion. Robert M. Pirsig, Zen and the Art of Motorcycle Maintenance --------------------------------------------------------------------------------------------------------------------------- _______________________________________________ Monotone-devel mailing list [email protected] http://lists.nongnu.org/mailman/listinfo/monotone-devel
