favor narrow index keys

According to the DB2 trainer, narrow index keys are always better. Many principles in DB tuning have exceptions, not this one. The wider the key, the fewer entries per node page, the more layers in the B+tree, the more disk reads. Each layer usually [1] translates to one disk read.

Usually a point search on the index starts from the root, and reads one node per layer.

How wide is an index key? I asked the DB2 trainer. It’s pretty much the width of each index column added together. Compression on the width is doable.

[1] often the root node and the first layer are in memory and requires no disk read. Remember there are easily hundreds of indices in a machine.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s