[ZODB-Dev] Indexing: Query Optimization

[Tim Peters]

[Thomas Guettler]
> I developed a simple index using ZODB. Searching for single values is
> very fast. Searching big ranges is slow.
>
> Example: Search:
>
> customer_id=0815
> date_start=2001-01-01
> date_end=2004-12-31
>
> The index holds a btree which maps values to docids. The search for
> customer_id is very fast (Maybe 500 results) . But the range search from
> date_start to date_end is slow (Maybe 100.000 results).
>
> Up to now I use the intersection method of the BTree package to join the
> result with a logical AND.
>
> It would be better if the index would do the search for customer_id
> first, and then filter the result by deleting entries which are not in
> the given time period.
>
> Has anyone done such a "Query Optimization"? (Zope, Zope3, IndexCatalog,
> ...)
>
> Since btrees and btree ranges don't know their size, you need to do
> something like this:
> - do range searches at the end
> - if index "foo" is used, use it first, ...

Dieter Maurer wrote some packages I suspect you'd find interesting in this
respect:

    http://mail.zope.org/pipermail/zope/2004-August/152627.html