How does a hash table index work?
Hash tables are another data structure that you may see being used as indexes – these
indexes are commonly referred to as hash indexes. The reason hash indexes are used
is because hash tables are extremely efficient when it comes to just looking up values.
So, queries that compare for equality to a string can retrieve values very fast if they
use a hash index. For instance, the query we discussed earlier (SELECT * FROM
Employee WHERE Employee_Name = ‘Jesus’) could benefit from a hash index
created on the Employee_Name column. The way a hash index would work is that the
column value will be the key into the hash table and the actual value mapped to that
key would just be a pointer to the row data in the table. Since a hash table is basically
an associative array, a typical entry would look something like “Jesus => 0×28939″,
where 0×28939 is a reference to the table row where Jesus is stored in memory.
Looking up a value like “Jesus” in a hash table index and getting back a reference to
the row in memory is obviously a lot faster than scanning the table to find all the rows
with a value of “Jesus” in the Employee_Name column.