Special index rules

[Zsolt Kendi]

For the preconditions see topic "Table as fifo".

Easy expalnation: There is a table where C1 column is a PRIMARY KEY, C2 column is a INDEX, C3 is a unindexed string. In the table we have more than million record and its storage size more than 1GB.

Normally (I think) if we issue SELECT C1,C2,C3 WHERE C1=[rowref] then the engine search in index files for C1 index value and point directly onto main file record offset and provide record quickly. But looks like this is not true. I do not know why but for this operation the engine go trough the all huge data file and the execution take a long time. But if the record cached then provide it quickly.
This is normal?
It is poosible any option to access record quickly if it is not in cache?

if we issue SELECT C1,C2 WHERE C1=[rowref] then the engine has information in index file so do not need to access the huge data file. But looks like the C2 value provided with alogirthm above.
This is normal?
It is poosible any option to access record quickly if it is not in cache?