Hi ! Robert DiFalco wrote:
Right, the proof is that if I have an PKEY on ID and an index just on VALUE in MySQL then a query that would use both ID and VALUE works fine with just the index on VALUE.
An index is a means to speed up access to the data when the most identifying thing, the primary key, isn't known. Without an index, you would have to scan ("sequential search") all the data for the qualifying row; but with a suitable index you have some kind of reference from the index entry to the row of data.
The concept of an index does not specify what exactly this reference is. With InnoDB, it is the primary key AFAIK.
Depending on the optimizer, this then *may* allow for "index-only" strategies: If all fields you need are part of either the index list (VALUE) or the primary key (ID), then there is no need to access the "real" data once the index entry is available.
For Oracle, I need an explicit compound index (in addition to the PKEY) on (ID,VALUE).
AFAIK, Oracle uses some physical address to access the record, so the index on VALUE does not contain the ID fields. This also means you cannot use an index-only strategy if you need a part of ID which is not contained in VALUE.
Using physical addresses forces the system to update all index entries if a record location changed (say, because a field of variable length was updated and the record doesn't fit in its current location any longer, or because the data pages get compacted after deletions).
HTH, Jörg -- Joerg Bruehe, Senior Production Engineer MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
