Duplicate Index Issue - xf_poll_response Table

[jeetkune]

Affected version

2.3.6

XenForo 2.3.6 was installed cleanly without any updates. However, during my MariaDB performance analysis, I encountered an issue. Below is the problem:

I’ve analyzed the database schema using pt-duplicate-key-checker and identified a suboptimal index in the xf_poll_response table that could be optimized for better performance.

Issue Identified​

The table has:

• A PRIMARY KEY on poll_response_id (auto-increment)
• A secondary index poll_id_response_id on (poll_id, poll_response_id)

Since poll_response_id is already the PRIMARY KEY, including it as the second column in the poll_id_response_id index is redundant. The InnoDB engine automatically appends the primary key to all secondary indexes, so the current definition effectively creates a duplicate:

• Current index: (poll_id, poll_response_id)
• InnoDB’s internal representation: (poll_id, poll_response_id, poll_response_id) (redundant)