Thursday, November 6, 2008

You never need to cover the primary key in Innodb

In Innodb (unlike MyISAM), the primary key is stored as a lookup in every secondary index; therefore, there is never a need to add the primary key column to a secondary index in order to cover it.

In MyISAM secondary indexes store the internal row ID as a lookup, so this trick will not work and you need to explicitly add the primary key column if you want to cover it in an index.

Brief aside: in some database systems you can specify columns to add to the index pages without affecting the index structure or constraints. For example, an index like (some_unique_column, primary_key) could cover the primary key in the index without destroying the uniqueness constraint, since of course the primary key would be independently unique.

No comments: