As designed Redundant columns on index xf_poll_response.poll_id_response_id

[Xon]

Affected version

1.5.15

    CREATE TABLE xf_poll_response (
        poll_response_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
        poll_id INT UNSIGNED NOT NULL,
        response VARCHAR(100) NOT NULL,
        response_vote_count INT UNSIGNED NOT NULL DEFAULT 0,
        voters MEDIUMBLOB NOT NULL,
        PRIMARY KEY (poll_response_id),
        KEY poll_id_response_id (poll_id, poll_response_id)
    ) ENGINE = InnoDB CHARACTER SET utf8 COLLATE utf8_general_ci

The poll_id_response_id index should just be over the poll_id column, as InnoDB automatically includes the primary key as part of the index.

This affects XF2 as well

 

[Mike]

InnoDB doesn't duplicate the primary key when it's the last component of an index, so this isn't wasteful.

I believe this may only apply to MySQL < 5.6 (http://jorgenloland.blogspot.co.uk/2013/10/faq-innodb-extended-secondary-indexes.html) but historically, without specifying the primary key as a final part of the column, then MySQL could not guarantee that the secondary index was ordered by the primary key value and thus it can't be used for sorting.

As there won't really be downside to keeping this (and potential downside from removing, and generally being very similar otherwise), I'm going to say this is as designed.