For MySQL's InnoDB Engine...
Case 1 (slowest): No index. It will scan the entire table (all rows) to find one the row(s) with
name = 'Scott' and fetchsalary` from them.
INDEX(name). This will use the index to very efficiently the Scott row(s), then fetch the rows and get the salarys.
Case 3 (fastest):
INDEX(name, salary). Now the entire query can be performed inside just the index -- without reaching over to the rest of the columns in the data. More This is called a "covering" index. More precisely: If all the columns needed for the entire
SELECT are found in the
INDEX, then only the index need be touched, not the data.
The Data is in one BTree, ordered by the
PRIMARY KEY. An
INDEX is in a different BTree, ordered by the index.
BTrees are very efficient for finding a specific row or reading consecutive rows based on the index. See the Wikipedia article on BTree. This is also a good reference: https://www.percona.com/files/presentations/percona-live/london-2011/PLUK2011-b-tree-indexes-and-innodb.pdf
For other databases, there could be differences.
How a row is stored
Case 1 (most situations): All the columns of a given table "record" (aka "row") are stored together in one contiguous set of dozens, maybe hundreds, of bytes. And this is all sitting in the same "block" along with perhaps a hundred other rows. So, when you grab any column from the row, all the rest of the columns are already readily available. Your example of an
Employee table probably falls into this Case. RDBMS optimization focuses on being able to get an entire row quickly, but sacrifices in not being able to get an entire column quickly.
Case 2: In some DB implementations (including MySQL's InnoDB) big columns (
BLOB) may be stored 'off record'. That is, they are put in some other block(s). Advantages () it allows for scanning rows quickly when you don't need those bulky pieces; () typically you will fetch the bulky columns for only one row at a time.
Case 3: There is a different type of database organization, with names like "Columnstore". This pivots the data so that it is easy to get all the values for a column quickly, but loses the ability to quickly get all the columns of a single row.
Focus on Case 1, since it is very common.