MySQL Forums
Forum List  »  Falcon

Re: Index only scans
Posted by: Ann Harrison
Date: May 04, 2007 04:47PM

Alas, further thought revealed that we still have to look at the records for several reasons. The most compelling is that after a crash, entries may remain in the index for records that have been deleted or modified, but were preserved in memory for older transactions. The record versions indicated by the index will be gone, but there's no automatic mechanism for removing the 'dead' entries.

Since we have to look at the records anyway, we do a few unusual things with indexes.

There can be more than one record with the same value, even in unique indexes, so we treat all index lookups as range retrievals, and sort out which are actually significant after reading the data. That allows us to be kind of loose with values stored as index keys.

All numbers are represented by double precision floating point equivalents - not exact, due to the decimal/binary conversion of fractions. The imprecision goes beyond the decimal portion in the case of values with more than 15 digits of precision. When we get in place metadata updates, we will be able to change an indexed field from numeric (10,2) to numeric (20,5) without changing either the stored data or the indexes.

We plan to eliminate all constraints on index key size, doing "prefix" indexing when the key is too large for the page size. That's not yet implemented, but will eliminate a problem with changing page sizes - when you reduce the page size, some indexes may be less effective, but all will survive the change.

For these reasons, and others I've missed, we really do have to check the stored record even if it isn't in memory.

Regards,


Ann

Options: ReplyQuote


Subject
Written By
Posted
April 25, 2007 09:03PM
May 04, 2007 11:05AM
Re: Index only scans
May 04, 2007 04:47PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.