indexing

[Douglas Tody]

Alex Szalay wrote:
> This could be more subtle, e.g. if we have a three part composite key, how
> would we note it? Say we have a primary key built on (run,camcol,field) just
> to stay with SDSS,
> The three of them together for an index, also if I filter on run, it would
> use an index, but not if I used a filter on field alone. How would you
> specify these columns in this case? Indexed or not? Or only run is indexed?
>
> --Alex
>   
The proposal is for a two-layer description of indexes.  At the simplest
level we would flag in TAP_SCHEMA.columns individual columns that
are indexed (but only the first field of a multi-field index key).
The more general solution would be to add another schema table that
describes indexes fully.  The simpler solution would allow some useful
indexing information to be visible in the minimum table metadata to
help guide simple client queries.  The more general scheme is needed
to fully understand indexing for more complex database schemas.

Note that TAP_SCHEMA does not currently have a general scheme for
describing indexes (but it does have the "indexed" attribute).
If we want to get this in now we would have to add it to TAP_SCHEMA
and possibly also VODataService.  Otherwise we could still have it
in our initial TAP implementations, but only as a prototype extension
(along with function description and other advanced metadata).

        - Doug


> -----Original Message-----
> From: Douglas Tody [mailto:dtody at nrao.edu] 
> Sent: Wednesday, May 27, 2009 4:53 PM
> To: Patrick Dowler; Gerard; Francois Ochsenbein
> Cc: DAL mailing list
> Subject: TAP: indexing
>
> Hi -
>
> More on the "indexed" attribute which came up in the discussions today.
> The intended usage in TAP_SCHEMA would be to indicate that on very
> large tables (catalogs usually) inclusion of such a field in a query
> could greatly speed up the query.  If this is not the case, e.g., the
> field is not primary in an index key, then it would not be flagged as
> "indexed" in this simple fashion.  Flagging a field as indexed does
> not necessarily imply a native SQL DBMS index, it could be a custom
> astronomy index (e.g. HTM, whatever) instead.  Users and client
> applications do not care.  This is one reason why we need this,
> rather than just describing the native DBMS indexes.  We could also
> do the latter of course, with a separate optional schema table, to
> inform more advanced queries.  But if a table field is indexed by
> itself it would be flagged as "indexed".
>
>  	- Doug
>