InnoDB: Is a covering index in the form of (PK, col1, col2, col3) redundant? Table structure for config:


property (primary key)
value
description


Query:

SELECT property, value FROM config


If I put a covering index on (property, value) the optimizer still chooses the PRIMARY index, but the extra in EXPLAIN is NULL. If I tell the optimizer to use my covering index, the extra in EXPLAIN is USING INDEX.

What's really happening here? Why is the optimizer choosing the PRIMARY index over my covering index by default? Am I avoiding disk IO by telling the optimizer to use my covering index?
PRIMARY index in InnoDB is a tree where the primary key field (property) is a key and remaining columns are the value. In a secondary index the key are the index fields and the value is a primary key. In your case the secondary index has fields (property, value, and property). (property, value) is a key and property is a value.
The secondary index is larger and redundant, so MySQL make a right decision to use the PRIMARY index.

Check out a presentation I gave on InnoDB indexes structure for more details.
https://www.slideshare.net/akuzminsky/efficient-indexes-in-mysql