Make sure we're using 100% efficient queries / indexes

[Christopher Allan Webber]

Every query we use should be able to efficiently use an index, yet at the same time, we shouldn't create superflous indexes.

Related:

• http://kylebanker.com/blog/2010/09/21/the-joy-of-mongodb-indexes/
• http://www.mongodb.org/display/DOCS/Indexing+Advice+and+FAQ
• http://www.mongodb.org/display/DOCS/Indexes

Especially, we should play with using explain:

http://www.mongodb.org/display/DOCS/Indexing+Advice+and+FAQ#IndexingAdviceandFAQ-Use{{explain}}.

Assigning to myself, but feel free to claim this if you're excited about investigating.

[Caleb Davis]

This bug inspired the following update to #360:

http://bugs.foocorp.net/issues/360#note-33

Here's what I propose:

• place at least one example of a proper search query in a comment alongside a new index
• assert within a unit test that the example query uses the target index
• do the analogous thing for new queries. name the target index in a comment alongside the query, and provide a unit test

These requirements should be met before merging a new index/query. The merger can then focus on evaluating the cost/benefit. I'll add unit tests and comments on #360, at least.

It's rather rigid, I suppose, but I think we need some system. Otherwise this bug could arguably be opened every time someone makes a new query or index. Under the proposed system, we can be confident the queries are locked down by unit tests. When we need a new query we could scan the indices to see if there is a suitable one already available. Likewise, we would have a way to update all the appropriate queries if we have to get rid of or change an index.

[Christopher Allan Webber]

I approve of that idea.

[Will Kahn-Greene]

We release 0.0.4, so I'm bumping this to 0.0.5.

[Elrond]

I doubt this gets adressed for 0.2.0.

And as we're concentrating on SQL now, I've set this to Low priority. It might get revisited in the SQL world again, with a lot of different meaning then. But until that time, it's surely Low priority.

[Will Kahn-Greene]

The original url for this bug was ​http://bugs.foocorp.net/issues/432 .
Relations:
#241: related, #55: related, #31: blocked, #379: blocked

[Christopher Allan Webber]

This is technically mostly relevant, just in the SQL world now. Elrond and I said we'd discuss soonish.

[Elrond]

We will try to add some indexes to the sql models as we go, but this really is currently low priority and we're not aiming to adress this for the next release (because the next release aims at "get sql running").

[Christopher Allan Webber]

I think we put some time into making efficient indexes, and I'm pretty sure that we'll update indexes from real actual experience as we go from here on out. I'm closing this ticket!