> Some more thoughts on this topic. Let's say I have a table:
>
> CREATE table1
> primarykeyinteger INTEGER PRIMARY KEY,
> timestamp1 TIMESTAMP,
> payload1 VARCHAR(128),
> payload2 VARCHAR(128)
> CREATE INDEX myindex1 ON table1 (timestamp1)
>
> According to tests with EXPLAIN, I found that the number of
> pages accessed
> with:
>
> select primarykeyinteger
> from table1 where timestamp1 >= {ts '200-02-09 11:00:00' }
>
> is equal to this query
>
> select *
> from table1 where timestamp1 >= {ts '200-02-09 11:00:00' }
>
> Ok, I'm not trained in DB theory... but it seems to me that the I/O
> pagecount should not be the same? However, EXPLAIN is giving
> me the same
> pagecount. even though the first query is not fetching the
> payload1/payload2 fields?
The primary key is part of the table row. Further, the table is always sorted after
the primary key. That means, that to get the contents of the primary key, the datapage
has to be read. If you select only fields that are part of the secondary index, then
only the index is read. What does
explain select timestamp1 from table1 where timestamp1 >= {ts '2000-02-09 11:00:00' }
report?
> Questions:
>
> 1. Is Explain only telling me the pagecount for reading
> the index, not the pagecount of fetching the actual fields
> from the table?
No.
> 2. Does MaxDB not realize that it can just get the primarykeyintger
> values reading directly from the index - and does't have to
> go actually read the record? A feature 'not yet implemented'?
There is no index that represents the primary key. Only secondary indexes are located
in their own B* trees.
> 3. Is there some name for 'fetch values only from index
> and not records' in database theory terminology?
It depends on the database product. Some call it INDEX ONLY, KEY ONLY and so on. But
it's nothing you can force by special SQL syntax or optimizer hints. The DBMSes
including MaxDB do it automagically.
--
MaxDB Discussion Mailing List
For list archives: http://lists.mysql.com/maxdb
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
