maria-developers team mailing list archive

[worklog-noreply@xxxxxxxxxxxx]

-----------------------------------------------------------------------
                              WORKLOG TASK
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
TASK...........: Partial Range Indx
CREATION DATE..: Thu, 28 Jul 2011, 10:44
SUPERVISOR.....: 
IMPLEMENTOR....: 
COPIES TO......: 
CATEGORY.......: Maria-RawIdeaBin
TASK ID........: 233 (http://askmonty.org/worklog/?tid=233)
VERSION........: WorkLog-4.0
STATUS.........: Un-Assigned
PRIORITY.......: 60
WORKED HOURS...: 0
ESTIMATE.......: 0 (hours remain)
ORIG. ESTIMATE.: 0

PROGRESS NOTES:



DESCRIPTION:

A way to make Indices significantly useful in some usage scenarios:

Scoping the index to a specific range of values. This can be useful if some
queries are only performed on a specific subset of the rows.

Example: A process that implements a state machine for each row. All finished
tasks have the state -1. To lookup the next task to perform only states > -1
have to be considered. Creating an index for the criteria to find the next task
quickly would also index all the finished tasks. Which can make index creation
expensive and waste several Gigabytes of diskspace (assuming millions of entries
in the DB).

While partitioning promises similar effects (a second axis for query
optimizations) it is rendered utterly useless by its restriction that the
partioning index must occur in *all* primary and unique keys which means one
cannot define a large natural key via unique and a small artificial ID as
primary key and then partition by an unrelated aspect (such as the state in my
example).

Range-restricted indices would allow similar optimizations: specify a
range-restricted column as first column and then add the remaining criteria as
other columns.


ESTIMATED WORK TIME

ESTIMATED COMPLETION DATE
-----------------------------------------------------------------------
WorkLog (v4.0.0)