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://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/Indexing+Advice+and+FAQ>`_
-  `http://www.mongodb.org/display/DOCS/Indexes <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 <http://www.mongodb.org/display/DOCS/Indexing+Advice+and+FAQ#IndexingAdviceandFAQ-Use%7B%7Bexplain>`_}}.

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 </issues/360>`_:

`http://bugs.foocorp.net/issues/360#note-33 <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 </issues/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!