Database indexing on non-key field

Question

I have a question regarding this answer.
I also posted this question as a comment to that answer, but didn't get any response.

It states that in the case of indexing on a non-key field, the schema would be:

Field name       Data type      Size on disk
firstName        Char(50)       50 bytes
(record pointer) Special        4 bytes

My question is that why is there a need to store firstName again? Isn't the pointer enough? We'll just have to simply change the sorting comparison function to compare on (*pointer).firstName, rather than firstName.

In theory, it is not needed: the char(50) field could be pulled from the basetable. In practice, this would cause **both** the basetable and the index to be pulled fron disk, costing more IO bandwith and bufferspace. — wildplasser, Mar 02 '16 at 15:06
@wildplasser Since I'm not getting any answers, would you like to convert your comment to an answer so that I can close the question? — Anmol Singh Jaggi, Mar 09 '16 at 13:59

score 0 · Accepted Answer · edited May 23 '17 at 12:31

0

Quoting the answer given as a comment by @wildplasser :

In theory, it is not needed: the char(50) field could be pulled from the basetable. In practice, this would cause both the basetable and the index to be pulled fron disk, costing more IO bandwith and bufferspace.

edited May 23 '17 at 12:31

Community

1
1

answered Mar 31 '16 at 03:02

Anmol Singh Jaggi

8,376
4
36
77

Database indexing on non-key field

1 Answers1