Closed Duplicate Index Issue - xf_poll_response Table

jeetkune

jeetkune

Member
Licensed customer
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)
1743790983726.webp
 
Automated triage: As designed

Thank you for reporting this issue. InnoDB doesn't store the primary key twice when an index already names it, so this index is stored the same as an index on poll_id alone, and changing it would not improve performance. An earlier report of the same index was closed as working as designed.

This is an automated assessment. A member of staff may review and update it.
 
Back
Top Bottom